package com.scalar.chiptrack.sales.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.CallableStatement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.StringTokenizer;
import java.util.NoSuchElementException;

import com.scalar.ScalarException;

import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.Mailer;

import com.scalar.chiptrack.commons.Address;
import com.scalar.chiptrack.commons.Location;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.commons.Labels;
import com.scalar.chiptrack.operations.vendor.dao.VendorDAO;
import com.scalar.chiptrack.users.User;
import com.scalar.chiptrack.sales.RepOffice;
import com.scalar.chiptrack.sales.SalesPerson;
import com.scalar.chiptrack.sales.ContactPerson;
import com.scalar.chiptrack.sales.Region;
import com.scalar.chiptrack.sales.SalesRep;
import com.scalar.chiptrack.sales.Customer;
import com.scalar.chiptrack.sales.Quotation;
import com.scalar.chiptrack.sales.ShippingMethod;
import com.scalar.chiptrack.sales.SalesOrder;
import org.apache.struts.util.LabelValueBean;
import org.apache.struts.util.MessageResources;

public class CustomerDAO
{

    public CustomerDAO() { }
    /**
     * Get All the customers
     *
     * @return customer list
    */
    public static ArrayList getAllCustomers() throws ScalarException
    {
        //Getting the customer list from CT
        String GETALL_VENDORS = "select Customer_ID,C_name,C_Contact_Person,C_Region from Customer WHERE C_Disabled = 1 ORDER BY C_Name";
        return ((ArrayList) getCustomers(GETALL_VENDORS));
    }

    /**
    * Get the customers
    * @return customer list
    *
    */
    public static ArrayList getAllCustomers(String customerName, User user) throws ScalarException
    {
        if( customerName != null ){
            customerName = customerName.trim() ;
        }

        //Getting the customer list from CT
        String SEARCH_CUSTOMERS = "";
        if ( user != null && user.getUserType() == Constants.USER_TYPE_CUSTOMER )
        {
            SEARCH_CUSTOMERS = "Select Customer_ID,C_Name,C_Contact_Person,C_Region from Customer Where C_Name like '" + StringUtils.formatToHandleQuote(customerName) + "%' and C_Disabled = 1 and CUSTOMER_ID = " + user.getCustomerId() + " ORDER BY C_Name";
        }
        else if ( user != null & user.getUserType() == Constants.USER_TYPE_SALES_PERSON )
        {
            SEARCH_CUSTOMERS = "Select C.Customer_ID,C.C_Name,C.C_Contact_Person,C.C_Region from Customer C, CUSTOMER_SALESPERSON_RELATIONSHIP CSR, CUSTOMER_TYPE_RELATIONSHIP CTR Where C.C_Name like '" + StringUtils.formatToHandleQuote(customerName) + "%' and C.C_Disabled = 1 AND CSR.CUSTOMER_ID = C.CUSTOMER_ID AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 1 AND CSR.SALESPERSON_ID IN (SELECT SALESPERSON_ID FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES(" + user.getSalesPersonId() + ")) AND CSR.RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active') ";
            if ( ConfigManager.isLoadEndCustomersForNewSales() )
            {
                SEARCH_CUSTOMERS += " UNION Select C.Customer_ID,C.C_Name,C.C_Contact_Person,C.C_Region from Customer C, CUSTOMER_SALESPERSON_RELATIONSHIP CSR, CUSTOMER_TYPE_RELATIONSHIP CTR Where C.C_Name like '" + StringUtils.formatToHandleQuote(customerName) + "%' and C.C_Disabled = 1 AND CSR.CUSTOMER_ID = C.CUSTOMER_ID AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 2 AND CSR.SALESPERSON_ID IN (SELECT SALESPERSON_ID FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES(" + user.getSalesPersonId() + ")) AND CSR.RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active') ";
            }
            if ( ConfigManager.isLoadEndUsersForNewSales() )
            {
                SEARCH_CUSTOMERS += " UNION Select C.Customer_ID,C.C_Name,C.C_Contact_Person,C.C_Region from Customer C, CUSTOMER_SALESPERSON_RELATIONSHIP CSR, CUSTOMER_TYPE_RELATIONSHIP CTR Where C.C_Name like '" + StringUtils.formatToHandleQuote(customerName) + "%' and C.C_Disabled = 1 AND CSR.CUSTOMER_ID = C.CUSTOMER_ID AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 3 AND CSR.SALESPERSON_ID IN (SELECT SALESPERSON_ID FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES(" + user.getSalesPersonId() + ")) AND CSR.RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active') ";
            }
            SEARCH_CUSTOMERS += " ORDER BY C_Name";
        }
        else
        {
            SEARCH_CUSTOMERS = "Select C.Customer_ID, C.C_Name, C.C_Contact_Person, C.C_Region from Customer C, CUSTOMER_TYPE_RELATIONSHIP CTR Where C_Name like '" + StringUtils.formatToHandleQuote(customerName) + "%' and C_Disabled = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 1 ";
            if ( ConfigManager.isLoadEndCustomersForNewSales() )
            {
                SEARCH_CUSTOMERS += " UNION Select C.Customer_ID, C.C_Name, C.C_Contact_Person, C.C_Region from Customer C, CUSTOMER_TYPE_RELATIONSHIP CTR Where C_Name like '" + StringUtils.formatToHandleQuote(customerName) + "%' and C_Disabled = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 2 ";
            }
            if ( ConfigManager.isLoadEndUsersForNewSales() )
            {
                SEARCH_CUSTOMERS += " UNION Select C.Customer_ID, C.C_Name, C.C_Contact_Person, C.C_Region from Customer C, CUSTOMER_TYPE_RELATIONSHIP CTR Where C_Name like '" + StringUtils.formatToHandleQuote(customerName) + "%' and C_Disabled = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 3 ";
            }
            SEARCH_CUSTOMERS += " ORDER BY C_Name";
        }
        return ( ( ArrayList ) getCustomers( SEARCH_CUSTOMERS ) );

    }
    
    public static ArrayList getAllDistributorNames() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList customers = new ArrayList();
        int customerId = 0;
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("select Customer_ID, C_Name from Customer where C_Disabled = 1 and C_IS_Distributor = 1 ORDER BY C_Name");
            rs = pstmt.executeQuery();
            //customers.add( new org.apache.struts.util.LabelValueBean( "--Select Distributor--", "0" ) );
            while( rs.next() )
            {
                customerId = rs.getInt("Customer_ID");
                customers.add( new org.apache.struts.util.LabelValueBean( rs.getString( "C_Name" ), Integer.toString( customerId ) ) );
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Distributors from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return customers;
   }

    public static ArrayList getAllCustomerNames() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList customers = new ArrayList();
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select Customer_ID,C_Name from Customer where C_Disabled = 1 ORDER BY C_Name" );
            rs = pstmt.executeQuery();
            customers.add( new org.apache.struts.util.LabelValueBean( "All Customers", "0" ) );
            while( rs.next() )
            {
                int customerId = rs.getInt( "Customer_ID" );
                customers.add( new org.apache.struts.util.LabelValueBean( rs.getString( "C_Name" ), Integer.toString( customerId ) ) );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Customers from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return customers;
    }


    public static ArrayList getAllCustomerNamesNoStringsAttached() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList customers = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select Customer_ID,C_Name from Customer where C_Disabled = 1 ORDER BY C_Name" );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                int customerId = rs.getInt( "Customer_ID" );
                customers.add( new org.apache.struts.util.LabelValueBean( rs.getString( "C_Name" ), Integer.toString( customerId ) ) );
            }

        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Customers from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return customers;

    }


    /**
     * Get All the customers or customer by search parameter
     *
     * @param customerName Search paramter
     * @return customer list
     */
    public static ArrayList getAllCustomers( String customerName, String region, String repOffice ) throws ScalarException
    {

        String SEARCH_CUSTOMERS = "Select Customer_ID,C_name,C_Contact_Person,C_Region from Customer where C_Disabled=1";
        String SEARCH_STRING = null;

        //To display details when All CustomerName, Region,RepOffice  fields contain Empty values
        if( ( region.equals( "" ) ) && ( repOffice.equals( "" ) ) && ( customerName.equals( "" ) ) )
        {
            SEARCH_STRING = " ORDER BY C_Name";
        }
        else
        {
            SEARCH_STRING = " and C_Name like '" + StringUtils.formatToHandleQuote( customerName ) + "%' and C_Region like '" + region + "%' and C_Rep_Office like '" + repOffice + "%' ORDER BY C_Name";
        }
        SEARCH_CUSTOMERS = SEARCH_CUSTOMERS + SEARCH_STRING;

        return ( ( ArrayList ) getCustomers( SEARCH_CUSTOMERS ) );
    }


    /**
    * Get All Regions
    *
    * @return regoins list
    */
    public static ArrayList getAllRegions() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList regionsList = new ArrayList();
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select DISTINCT Region_Id, ltrim(Region_Name) Region_Name from Regions_Table where Deactivated = 0 or Deactivated is NULL order by ltrim(Region_Name)" );
            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                regionsList.add( new LabelValueBean( rs.getString( "Region_Name" ), rs.getString( "Region_Id" ) ) );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Regions from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return regionsList;
    }

    public static ArrayList getAllRegionNames() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList regionsList = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select DISTINCT ltrim(Region_Name) Region_Name from Regions_Table where Deactivated = 0 or Deactivated is NULL order by ltrim(Region_Name) " );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                regionsList.add(  rs.getString( "Region_Name" ) );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Regions from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return regionsList;
    }
     public static ArrayList<LabelValueBean> getAllRegionNameList() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<LabelValueBean> regionsList = new ArrayList<LabelValueBean>();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select DISTINCT ltrim(Region_Name) Region_Name from Regions_Table where Deactivated = 0 or Deactivated is NULL order by ltrim(Region_Name) " );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                regionsList.add( new LabelValueBean(rs.getString( "Region_Name" ), rs.getString( "Region_Name" ) ) );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Regions from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return regionsList;
    }

    /*
    * To get the corresponding list of RepOffices only that are in active state ,even we have getAllRepOffices()
    * because  getAllRepOffices() is using in REports in which we have to listout all RepOffices
    */
    public static ArrayList getAllRepOfficesForCustomer() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList repOfficesList = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select DISTINCT Rep_Office_Name from Rep_Offices where Deactivated =0 or Deactivated is NULL" );

            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                repOfficesList.add( rs.getString( "Rep_Office_Name" ) );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Rep Offices from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return repOfficesList;
    }

    public static ArrayList getAllSalesRepsForCustomer() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList salesList = new ArrayList();
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select DISTINCT Sales_Rep_Name from Sales_Rep_Table where Deactivated =0 or Deactivated is NULL" );
            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                salesList.add( rs.getString( "Sales_Rep_Name" ) );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Sales Reps from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return salesList;
    }

    public static ArrayList getAllSalesPersonsForCustomer() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList salesList = new ArrayList();
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select DISTINCT Sales_Person_Name from Sales_Persons_Table where Deactivated =0 or Deactivated is NULL" );
            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                salesList.add( rs.getString( "Sales_Person_Name" ) );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Sales Persons from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return salesList;
    }

    /*
    * To get the corresponding list of Regions only that are in active state for a particular customer id ,even we have getAllRegions()
    * because  getAllRegions() is using in REports in which we have to listout all regions
    */
    public static ArrayList getAllRegionsForCustomer( int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList regionsList = new ArrayList();
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select DISTINCT Region_Name from regions where ( Deactivated = 0 or Deactivated is NULL ) and customer_Id = ? " );
            pstmt.setInt( 1, customerId );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                regionsList.add( rs.getString( "Region_Name" ) );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Regions from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return regionsList;
    }

    /*
    * To get the corresponding list of RepOffices only that are in active state for a particular customer id,even we have getAllRepOffices()
    * because  getAllRepOffices() is using in REports in which we have to listout all RepOffices
    */
    public static ArrayList getAllRepOfficesForCustomer( int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList repOfficesList = new ArrayList();

        try
        {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select DISTINCT Rep_Office_Name from Rep_Offices where ( Deactivated = 0 or Deactivated is NULL ) and customer_Id = ?" );
            pstmt.setInt( 1, customerId );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                repOfficesList.add( rs.getString( "Rep_Office_Name" ) );
            }

        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Rep Offices from database", sqlex );
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return repOfficesList;
    }

    /*
    * To get the corresponding list of SAles Persons only that are in active state for a particular customer id,even we have loadAllSalesPersons()
    * because  loadAllSalesPersons() is using in REports in which we have to listout all SalesPersons
    */
    public static ArrayList getAllSalesPersonsForCustomer( int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList salesList = new ArrayList();
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select DISTINCT Sales_Person_Name from Sales_Persons_Table where ( Deactivated = 0 or Deactivated is NULL ) and customer_Id = ?" );
            pstmt.setInt( 1, customerId );
            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                salesList.add( rs.getString( "Sales_Person_Name" ) );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Sales Persons from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return salesList;
    }

    public static ArrayList getAllSalesRepsForCustomer( int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList salesList = new ArrayList();

        try
        {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select DISTINCT Sales_Rep_Name from Sales_Rep_Table where ( Deactivated = 0 or Deactivated is NULL ) and customer_Id = ?" );
            pstmt.setInt( 1, customerId );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                salesList.add( rs.getString( "Sales_Rep_Name" ) );
            }

        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Sales Reps from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return salesList;
    }


    public static ArrayList getAllRegionsWithActions() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Region region = null;
        ArrayList regionsList = new ArrayList();
        try
        {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select Region_Id, Region_Name, Deactivated from Regions_Table Order by Region_Name" );
            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                region = new Region();
                region.setRegionId( rs.getInt( "Region_Id" ) );
                region.setRegion( rs.getString( "Region_Name" ) );
                region.setDisabled( rs.getInt( "Deactivated" ) == 0 ? true:false );
                regionsList.add( region );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Regions from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return regionsList;
    }

    public static void deleteRegion( int regionId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.deleteRegion" ) );
            pstmt.setInt( 1, regionId );
            pstmt.executeUpdate();
           success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not delete Region information into database", sqle );
        }
        finally
        {
            try
            {
                if( conn != null )
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch( SQLException ignore )
            {
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
    }


    public static void activateRegion( int regionId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.activateRegion" ) );
            pstmt.setInt( 1, regionId );
            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not activate the Region", sqle );
        }
        finally
        {
            try
            {
                if( conn != null )
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch( SQLException ignore )
            {
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
    }

    public static ArrayList getAllRepOfficesWithActions( int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        RepOffice repofffice = null;
        ArrayList repoffficesList = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select DISTINCT Rep_Office_Name,Deactivated from Rep_Offices where customer_id = ?" );
            pstmt.setInt( 1, customerId );
            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                repofffice = new RepOffice();
                repofffice.setRepOffice( rs.getString( "Rep_Office_Name" ) );
                repofffice.setDisabled( rs.getInt( "Deactivated" ) == 0 ? true:false );
                repoffficesList.add( repofffice );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get RepOffices from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return repoffficesList;
    }

    public static void deleteRepOffice( String repOfficeName, int customerId ) throws ScalarException
    {
        if( repOfficeName == null )
        {
            return;
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.deleteRepOffice" ) );

            pstmt.setString( 1, repOfficeName );
            pstmt.setInt( 2, customerId );
            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not delete RepOffice information into database", sqle );
        }
        finally
        {
            try
            {
                if( conn != null )
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch( SQLException ignore )
            {
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
    }

    public static void activateRepOffice( String repOfficeName, int customerId ) throws ScalarException
    {
        if( repOfficeName == null )
        {
            return;
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.activateRepOffice" ) );

            pstmt.setString( 1, repOfficeName );
            pstmt.setInt( 2, customerId );
            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not activate the RepOffice", sqle );
        }
        finally
        {
            try
            {
                if( conn != null )
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch( SQLException ignore )
            {
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
    }


    public static ArrayList getAllSalesPersonsWithActions( int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        SalesPerson salesperson = null;
        ArrayList salespersonList = new ArrayList();
        try
        {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select DISTINCT sales_Person_Name, Deactivated from sales_Persons_Table where customer_Id = ?" );
            pstmt.setInt( 1, customerId );
            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                salesperson = new SalesPerson();
                salesperson.setSalesPerson( rs.getString( "Sales_Person_Name" ) );
                salesperson.setDisabled( rs.getInt( "Deactivated" ) == 0 ? true:false );
                salespersonList.add( salesperson );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get SalesPersons from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return salespersonList;
    }

    public static SalesPerson getSalesPersonDetails( DBTransaction dbtransaction, String salesPersonName, int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        SalesPerson salesperson = null;
        int addressId = -1;


        try
        {
            if( dbtransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbtransaction.getConnection();
            }
            pstmt = conn.prepareStatement( "select * from sales_Persons_Table where customer_Id = ? and Sales_Person_Name = ?" );
            pstmt.setInt( 1, customerId );
            pstmt.setString( 2, salesPersonName );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                salesperson = new SalesPerson();
                salesperson.setSalesPerson( rs.getString( "Sales_Person_Name" ) );
                addressId = rs.getInt( "Address_Id" );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get SalesPersons from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        if( addressId > 0 )
        {
            Address address = CustomerDAO.getAddress( addressId );
            if( address != null )
            {
                address.setAddressId( addressId );
            }
            salesperson.setAddress( address );
        }
        return salesperson;
    }

    public static void deleteSalesPerson( String salesPersonName, int customerId ) throws ScalarException
    {
        if( salesPersonName == null )
        {
            return;
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.deleteSalesPerson" ) );

            pstmt.setString( 1, salesPersonName );
            pstmt.setInt( 2, customerId );
            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not delete SalesPerson information into database", sqle );
        }
        finally
        {
            try
            {
                if( conn != null )
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch( SQLException ignore )
            {
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
    }


    public static void activateSalesPerson( String salesPersonName, int customerId ) throws ScalarException
    {
        if( salesPersonName == null )
        {
            return;
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.activateSalesPerson" ) );

            pstmt.setString( 1, salesPersonName );
            pstmt.setInt( 2, customerId );
            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not activate the SalesPerson", sqle );
        }
        finally
        {
            try
            {
                if( conn != null )
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch( SQLException ignore )
            {
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
    }

    public static ArrayList getAllSalesRepsWithActions( int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        SalesRep salesrep = null;
        ArrayList salesrepList = new ArrayList();
        try
        {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select DISTINCT sales_Rep_Name, Deactivated from Sales_Rep_Table where customer_Id = ?" );
            pstmt.setInt( 1, customerId );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                salesrep = new SalesRep();
                salesrep.setSalesRep( rs.getString( "Sales_Rep_Name" ) );
                salesrep.setDisabled( rs.getInt( "Deactivated" ) == 0 ? true:false );
                salesrepList.add( salesrep );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get SalesReps from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return salesrepList;
    }

    public static void deleteSalesRep( String salesRepName, int customerId ) throws ScalarException
    {
        if( salesRepName == null )
        {
            return;
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.deleteSalesRep" ) );
            pstmt.setString( 1, salesRepName );
            pstmt.setInt( 2, customerId );
            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not delete SalesPerson information into database", sqle );
        }
        finally
        {
            try
            {
                if( conn != null )
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch( SQLException ignore )
            {
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
    }

    public static void activateSalesRep( String salesRepName, int customerId ) throws ScalarException
    {
        if( salesRepName == null )
        {
            return;
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.activateSalesRep" ) );

            pstmt.setString( 1, salesRepName );
            pstmt.setInt( 2, customerId );
            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not activate the SalesPerson", sqle );
        }
        finally
        {
            try
            {
                if( conn != null )
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch( SQLException ignore )
            {
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
    }

    public static ArrayList getAllContactPersonsForCustomer() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList contactPersonsList = new ArrayList();


        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("select DISTINCT Contact_Person from Contact_Persons_Table where Deactivated =0 or Deactivated is NULL");

            rs = pstmt.executeQuery();

            while(rs.next())
            {
                contactPersonsList.add(rs.getString("Contact_Person"));
            }

        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Contact Persons from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return contactPersonsList;
    }


    public static ArrayList getAllContactPersonsForCustomer( int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList contactPersonsList = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "Select DISTINCT Contact_Person from Customer_Contact_Persons_Table where ( Deactivated = 0 or Deactivated is NULL ) and Customer_Id = ?" );
            pstmt.setInt( 1, customerId );
            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                contactPersonsList.add( rs.getString( "Contact_Person" ) );
            }

            /*while( rs.next() )
            {
                contactPersonsList.add( rs.getString( "Contact_Person" ) );
            }*/
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Contact Persons from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return contactPersonsList;
    }

    public static ArrayList getAllContactPersonsForAddress( int AddressId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList contactPersonsList = new ArrayList();

        try
        {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "Select DISTINCT Contact_Person from Address_Contact_Persons_Table where ( Deactivated = 0 or Deactivated is NULL ) and Address_Id = ?" );
            pstmt.setInt( 1, AddressId );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                contactPersonsList.add( rs.getString( "Contact_Person" ) );
            }

        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Contact Persons from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return contactPersonsList;
    }

	public static ArrayList getAllContactPersonsForShipping(int shippingAddressId) throws ScalarException
	   {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		ArrayList contactPersonsList = new ArrayList();

		try {

				 conn = DataSource.getConnection();
				 pstmt = conn.prepareStatement("Select DISTINCT Contact_Person from Shipping_Contact_Persons_Table where ( Deactivated = 0 or Deactivated is NULL ) and Shipping_Address_Id = ?");
				 pstmt.setInt ( 1 , shippingAddressId );
				 rs = pstmt.executeQuery();

				  while(rs.next())
				  {
					contactPersonsList.add(rs.getString("Contact_Person"));
				  }

			}
			catch (Exception sqlex) {
				sqlex.printStackTrace();
				throw new ScalarException ("Could not get Contact Persons from database", sqlex);
			}
			finally {
				if (rs != null) {
					try { rs.close(); } catch (SQLException excp) {}
				}
				if (pstmt != null) {
					try { pstmt.close(); } catch (SQLException excp) {}
				}
				if (conn != null) {
					try { conn.close(); } catch (SQLException excp) {}
				}
			}

			return contactPersonsList;
	   }


    public static ArrayList getAllContactPersonsWithActions(int customerId , String contactPersonValue) throws ScalarException
    {
        Connection conn             = null;
        PreparedStatement pstmt     = null;
        ResultSet rs                = null;
        ContactPerson contactperson = null;
        ArrayList contactpersonList = new ArrayList();
        try
        {

            conn = DataSource.getConnection();

            if( "Customer".equalsIgnoreCase ( contactPersonValue.trim() ) )
            {
                pstmt = conn.prepareStatement( "select DISTINCT Contact_Person, Deactivated, Email from Customer_Contact_Persons_Table where customer_Id = ?" );
            }
            else if( "Address".equalsIgnoreCase( contactPersonValue.trim() ) )
            {
			    pstmt = conn.prepareStatement( "select DISTINCT Contact_Person, Deactivated, Email from Address_Contact_Persons_Table where Address_Id = ?" );
            }
          	pstmt.setInt ( 1 , customerId );

          	rs = pstmt.executeQuery();

            while(rs.next())
            {
                contactperson = new ContactPerson();
                contactperson.setContactPerson( rs.getString( "Contact_Person" ) );
                contactperson.setEmail( rs.getString( "Email" ) );
                contactperson.setDisabled( rs.getInt( "Deactivated" ) == 0 ? true :false );

                contactpersonList.add( contactperson );
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get ContactPersons from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return contactpersonList;
    }



    public static ArrayList getAllContactPersonsWithEmails(int customerId) throws ScalarException
	{
	    Connection conn = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
	    ContactPerson contactperson = null;
	    ArrayList contactpersonList = new ArrayList();


	    try
        {

		    conn = DataSource.getConnection();
		    pstmt = conn.prepareStatement("select DISTINCT Contact_Person, Deactivated, Email from Customer_Contact_Persons_Table where customer_Id = ? and ( Email is not NULL and Email <> '')");
		    pstmt.setInt ( 1 , customerId );
		    rs = pstmt.executeQuery();

		    while(rs.next())
		    {
			    contactperson = new ContactPerson();
			    contactperson.setContactPerson(rs.getString("Contact_Person"));
			    contactperson.setEmail(rs.getString("Email"));
			    contactperson.setDisabled(rs.getInt("Deactivated") == 0 ? true :false);
                contactpersonList.add(contactperson);
		    }
        }
		catch (Exception sqlex)
        {
			 sqlex.printStackTrace();
			 throw new ScalarException ("Could not get ContactPersons from database", sqlex);
		}
		finally
        {
			 if (rs != null)
             {
				 try { rs.close(); } catch (SQLException excp) {}
			 }
			 if (pstmt != null)
             {
				 try { pstmt.close(); } catch (SQLException excp) {}
			 }
			 if (conn != null)
             {
				 try { conn.close(); } catch (SQLException excp) {}
			 }
        }

		return contactpersonList;
    }
	//To get Customer , Billing , Shipping - Contact Person Emalids
	public static ArrayList getAllContactPersonsWithEmails( DBTransaction dbTransaction, SalesOrder salesOrder ) throws ScalarException
    {
	    Connection conn             = null;
        PreparedStatement pstmt     = null;
        ResultSet rs                = null;
        ContactPerson contactperson = null;
        ArrayList contactpersonList = new ArrayList();
        boolean success=false;

        try
		{
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            //This statement is for getting the Customer Contact person emails
            pstmt = conn.prepareStatement("SELECT DISTINCT Contact_Person, Email, Deactivated, Sales_Ack_Mail, Proforma_Invoice_Mail, Packing_Slip_Mail, Comm_Invoice_Mail, Quote_Ack_Mail, Rma_Ack_Mail  FROM Customer_Contact_Persons_Table WHERE  (Email IS NOT NULL) AND (Email <> '') AND (Customer_Id = ? ) and Deactivated <> 1");
            pstmt.setInt ( 1, salesOrder.getCustomerId() );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                contactperson = new ContactPerson();
                contactperson.setContactPerson(rs.getString("Contact_Person"));
                contactperson.setEmail(rs.getString("Email"));
                contactperson.setDisabled(rs.getInt("Deactivated") == 0 ? true : false);

                contactperson.setSalesAckMail ( rs.getString("Sales_Ack_Mail") != null &&  rs.getString("Sales_Ack_Mail").equals("0") ? true : false );
                contactperson.setProformaInvoice ( rs.getString("Proforma_Invoice_Mail") != null &&  rs.getString("Proforma_Invoice_Mail").equals("0") ? true : false );
                contactperson.setPackingSlip ( rs.getString("Packing_Slip_Mail") != null &&  rs.getString("Packing_Slip_Mail").equals("0") ? true : false );
                contactperson.setCommerInvoice ( rs.getString("Comm_Invoice_Mail") != null &&  rs.getString("Comm_Invoice_Mail").equals("0") ? true : false );
                contactperson.setQuoteAckMail ( rs.getString("Quote_Ack_Mail") != null &&  rs.getString("Quote_Ack_Mail").equals("0") ? true : false );
                contactperson.setRmaAckMail ( rs.getString("Rma_Ack_Mail") != null &&  rs.getString("Rma_Ack_Mail").equals("0") ? true : false );

                contactpersonList.add(contactperson);
            }
            if( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }    

            //This statement is for getting the SalesOrder Billing or Shipping Contact person emails
            pstmt = conn.prepareStatement("SELECT Contact_Person, Email, Deactivated, Sales_Ack_Mail, Proforma_Invoice_Mail, Packing_Slip_Mail, Comm_Invoice_Mail, Quote_Ack_Mail, Rma_ack_Mail  FROM Address_Contact_Persons_Table WHERE (Email IS NOT NULL) AND (Address_Id = ? OR Address_Id = ? ) AND (Email <> '') and Deactivated <> 1 Order By Address_Id");
            pstmt.setInt ( 1 , salesOrder.getBillingAddressId() );
            pstmt.setInt ( 2 , salesOrder.getShippingAddressId() );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                contactperson = new ContactPerson();
                contactperson.setContactPerson(rs.getString("Contact_Person"));
                contactperson.setEmail(rs.getString("Email"));
                contactperson.setDisabled(rs.getInt("Deactivated") == 0 ? true : false);

                contactperson.setSalesAckMail( rs.getString("Sales_Ack_Mail") != null &&  rs.getString("Sales_Ack_Mail").equals("0") ? true : false );
                contactperson.setProformaInvoice ( rs.getString("Proforma_Invoice_Mail") != null &&  rs.getString("Proforma_Invoice_Mail").equals("0") ? true : false );
                contactperson.setPackingSlip ( rs.getString("Packing_Slip_Mail") != null &&  rs.getString("Packing_Slip_Mail").equals("0") ? true : false );
                contactperson.setCommerInvoice ( rs.getString("Comm_Invoice_Mail") != null &&  rs.getString("Comm_Invoice_Mail").equals("0") ? true : false );
                contactperson.setQuoteAckMail ( rs.getString("Quote_Ack_Mail") != null &&  rs.getString("Quote_Ack_Mail").equals("0") ? true : false );
                contactperson.setRmaAckMail ( rs.getString("Rma_Ack_Mail") != null &&  rs.getString("Rma_Ack_Mail").equals("0") ? true : false );
            
                contactpersonList.add(contactperson);
            }

            success = true;

        }
        catch (Exception sqlex)
        {
             sqlex.printStackTrace();
             throw new ScalarException ("Could not get ContactPersons from database", sqlex);
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return contactpersonList;
    }
    
    // Added by Veeru on 17th September 2004
    public static String getSalesPersonEmail(DBTransaction dbTransaction, String salesPersonName, int customerId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String email = null;
        boolean success=false;


        try
        {
             //	Get the connection from regular datasource if its not in a manual transaction
             if (dbTransaction == null) {
                 conn = DataSource.getConnection();
             }
             else {
                 conn = dbTransaction.getConnection();
             }
            //This statement is for getting the Sales Person email
            //pstmt = conn.prepareStatement("SELECT Email from Address where address_id = ( SELECT Address_Id from Sales_Persons_Table where Sales_Person_Name = ? and customer_id = ?  )");
            pstmt = conn.prepareStatement("SELECT Email from Address where address_id = ( SELECT Address_Id from Sales_Persons where SalesPerson_Name = ? )");
            pstmt.setString ( 1 , salesPersonName);
            //pstmt.setInt(2, customerId);
            rs = pstmt.executeQuery();
            while(rs.next())
            {
                email = rs.getString("Email");
            }
            pstmt.close();
            pstmt = null;
            success = true;
        }
        catch (Exception sqlex)
        {
             sqlex.printStackTrace();
             throw new ScalarException ("Could not get SalesPerson Email from database", sqlex);
        }
        finally
        {
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return email;
    }

    public static ArrayList getAllContactPersonsWithEmails( DBTransaction dbTransaction, Quotation quotation ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ContactPerson contactperson = null;
        ArrayList contactpersonList = new ArrayList();
        boolean success = false;
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            //Thiss statement is for getting the Customer Contact person emails
            pstmt = conn.prepareStatement( "SELECT DISTINCT Contact_Person, Email, Deactivated FROM Customer_Contact_Persons_Table WHERE  (Email IS NOT NULL) AND (Email <> '') AND (Customer_Id = ? ) and Deactivated <> 1" );
            pstmt.setInt( 1, quotation.getCustomerId() );
            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                contactperson = new ContactPerson();
                contactperson.setContactPerson( rs.getString( "Contact_Person" ) );
                contactperson.setEmail( rs.getString( "Email" ) );
                contactperson.setDisabled( rs.getInt( "Deactivated" ) == 0 ? true:false );
                contactpersonList.add( contactperson );
            }
            rs.close();
            rs = null;
            pstmt.close();
            pstmt = null;

            //	This statement is for getting the SalesOrder Billing or Shipping Contact person emails
            pstmt = conn.prepareStatement( "SELECT Contact_Person, Email, Deactivated FROM Address_Contact_Persons_Table WHERE (Email IS NOT NULL) AND (Address_Id = ? OR Address_Id = ? ) AND (Email <> '') and Deactivated <> 1 Order By Address_Id " );
            pstmt.setInt( 1, quotation.getBillingAddressId() );
            pstmt.setInt( 2, quotation.getShippingAddressId() );
            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                contactperson = new ContactPerson();
                contactperson.setContactPerson( rs.getString( "Contact_Person" ) );
                contactperson.setEmail( rs.getString( "Email" ) );
                contactperson.setDisabled( rs.getInt( "Deactivated" ) == 0 ? true:false );
                contactpersonList.add( contactperson );
            }
            success = true;
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get ContactPersons from database", sqlex );
        }
        finally
        {

            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return contactpersonList;
    }


    //To deactivate particular contact Persons
    public static void deleteContactPerson(String contactPersonName , int customerId , String contactPersonValue) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;


        if (contactPersonName == null)
        {
            return;
        }

        try
        {
            conn = DataSource.getConnection();

            if( "Customer".equals( contactPersonValue.trim() ) )
            {
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.deleteCustomerContactPerson"));
            }
            else if( "Address".equals( contactPersonValue.trim() ) )
            {
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.deleteAddressContactPerson"));
            }

            pstmt.setString ( 1, contactPersonName );
            pstmt.setInt ( 2 , customerId );
            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle)
        {
           sqle.printStackTrace();
           throw new ScalarException ("Could not delete ContactPerson information into database", sqle);
        }
        finally
        {
           try
           {
               if (conn != null)
               {
                   if (success == true)
                   {
                       conn.commit();
                   }
                   else
                   {
                       conn.rollback();
                   }
               }
           }
           catch (SQLException ignore) {}

           if (pstmt != null)
           {
               try { pstmt.close(); } catch (SQLException ignore){}
           }
           if (conn != null)
           {
               try { conn.close(); } catch (SQLException ignore){}
           }
       }
    }

    /**
     * To activate particular Contact Persons
     */
    public static void activateContactPerson (String contactPersonName , int customerId , String contactPersonValue ) throws ScalarException
    {
        Connection conn          = null;
        PreparedStatement pstmt  = null;
        boolean success          = false;


        if ( contactPersonName == null )
        {
            return;
        }
        try
        {
            conn = DataSource.getConnection();

            if( "Customer".equalsIgnoreCase( contactPersonValue.trim() ) )
		    {
           	    pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.activateCustomerContactPerson"));
		    }
		    else if( "Address".equalsIgnoreCase( contactPersonValue.trim() ) )
		    {
				pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.activateAddressContactPerson"));
		    }

            pstmt.setString ( 1 , contactPersonName );
            pstmt.setInt ( 2 , customerId );
            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not activate the ContactPerson", sqle);
        }
        finally
        {
            try
            {
                if (conn != null)
                {
                    if (success == true)
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null)
            {
               try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null)
            {
               try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    //To get the Contact person data to edit
    public  static ContactPerson editContactPerson( String contactPersonName , int customerId , String contactPersonValue ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        ContactPerson contactPerson = new  ContactPerson();
        boolean success = false;

        if( contactPersonName == null )
        {
            return( null );
        }

        try
        {
            conn = DataSource.getConnection();

            if( "Customer".equalsIgnoreCase( contactPersonValue.trim() ) )
		    {
	           	pstmt = conn.prepareStatement ("select Contact_Person, Phone_Number, Cell_Number, Fax, Email, Address, Sales_Ack_Mail, Ship_Alerts, Proforma_Invoice_Mail, Packing_Slip_Mail, Comm_Invoice_Mail, Quote_Ack_Mail, Rma_Ack_Mail from Customer_Contact_Persons_Table where Contact_Person = ? and Customer_Id= ? ");
		    }
		    else if( "Address".equalsIgnoreCase( contactPersonValue.trim() ) )
		    {
		        pstmt = conn.prepareStatement ("select Contact_Person, Phone_Number, Cell_Number, Fax, Email, Address, Sales_Ack_Mail, Ship_Alerts, Proforma_Invoice_Mail, Packing_Slip_Mail, Comm_Invoice_Mail, Quote_Ack_Mail, Rma_ack_mail from Address_Contact_Persons_Table where Contact_Person = ? and Address_Id= ? ");
		    }
            pstmt.setString ( 1, contactPersonName );
            pstmt.setInt ( 2 , customerId );
            rs = pstmt.executeQuery();
            if(rs.next() )
            {
                contactPerson.setContactPerson( rs.getString("Contact_Person") );
                contactPerson.setPhoneNumber( rs.getString("Phone_Number") );
                contactPerson.setCellNumber( rs.getString("Cell_Number") );
                contactPerson.setFaxNumber( rs.getString("Fax") );
                contactPerson.setEmail( rs.getString("Email") ) ;
                contactPerson.setAddress( rs.getString("Address") );
                contactPerson.setSalesAckMail( rs.getString("Sales_Ack_Mail") != null &&  rs.getString("Sales_Ack_Mail").equals("0") ? true : false );
                contactPerson.setShipAlerts ( rs.getString("Ship_Alerts") != null &&  rs.getString("Ship_Alerts").equals("0") ? true : false );
                contactPerson.setProformaInvoice ( rs.getString("Proforma_Invoice_Mail") != null &&  rs.getString("Proforma_Invoice_Mail").equals("0") ? true : false );
                contactPerson.setPackingSlip ( rs.getString("Packing_Slip_Mail") != null &&  rs.getString("Packing_Slip_Mail").equals("0") ? true : false );
                contactPerson.setCommerInvoice ( rs.getString("Comm_Invoice_Mail") != null &&  rs.getString("Comm_Invoice_Mail").equals("0") ? true : false );
                contactPerson.setQuoteAckMail ( rs.getString("Quote_Ack_Mail") != null &&  rs.getString("Quote_Ack_Mail").equals("0") ? true : false );
                contactPerson.setRmaAckMail ( rs.getString("Rma_Ack_Mail") != null &&  rs.getString("Rma_Ack_Mail").equals("0") ? true : false );
            }

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get the ContactPerson Details to Edit", sqle);
        }
        finally
        {
           try
           {
               if (conn != null)
               {
                   if (success == true)
                   {
                       conn.commit();
                   }
                   else
                   {
                       conn.rollback();
                   }
               }
           }
           catch (SQLException ignore) {}

           if (rs != null)
           {
               try { rs.close(); } catch (SQLException ignore){}
           }
           if (pstmt != null)
           {
               try { pstmt.close(); } catch (SQLException ignore){}
           }
           if (conn != null)
           {
               try { conn.close(); } catch (SQLException ignore){}
           }
       }
       return contactPerson;
   }

    //To save contact person info in customer_persons_table
    public static void insertContactPerson(DBTransaction dbTransaction, int customerId, String contactperson,
                                           String phoneNumber, String cellNumber, String fax, String email,
                                           boolean salesAckMail, boolean shipAlerts, boolean proformaInvoice,
                                           boolean packingSlip, boolean commerInvoice, String address, String contactPersonValue, boolean quoteAckMail, boolean rmaAckMail) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        boolean success         = false;

      try
      {
          // Get the connection from regular datasource if its not in a manual transaction
          if( dbTransaction == null )
          {
              conn = DataSource.getConnection();
          }
          else
          {
              conn = dbTransaction.getConnection();
          }

		  if( "Customer".equalsIgnoreCase( contactPersonValue.trim() ) )
		  {
	          pstmt = conn.prepareStatement( SqlQueryMappings.get ( "sales.customer.insertCustomerContactPerson" ) );
			  pstmt.setInt( 1 , customerId );
		  }
		  else if( "Address".equalsIgnoreCase( contactPersonValue.trim() ) )
		  {
			  pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.customer.insertAddressContactPerson" ) );
			  pstmt.setInt( 1 , customerId );
		  }
          pstmt.setString ( 2 , (contactperson   != null   ? contactperson.trim() : "") );
          pstmt.setString ( 3 , (phoneNumber     != null   ? phoneNumber.trim()   : "") );
          pstmt.setString ( 4 , (cellNumber      != null   ? cellNumber.trim()    : "") );
          pstmt.setString ( 5 , (fax             != null   ? fax.trim()           : "") );
          pstmt.setString ( 6 , (email           != null   ? email.trim()         : "") );
          pstmt.setString ( 7 , (address         != null   ? address.trim()       : "") );
          pstmt.setInt    ( 8,  (salesAckMail    == false) ? 1 : 0 );
          pstmt.setInt    ( 9,  (shipAlerts      == false) ? 1 : 0 );
          pstmt.setInt    ( 10, (proformaInvoice == false) ? 1 : 0 );
          pstmt.setInt    ( 11, (packingSlip     == false) ? 1 : 0 );
          pstmt.setInt    ( 12, (commerInvoice   == false) ? 1 : 0 );
          pstmt.setInt    ( 13, (quoteAckMail   == false) ? 1 : 0 );
          pstmt.setInt    ( 14, (rmaAckMail   == false) ? 1 : 0 );
          pstmt.execute();

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not insert contactperson, \"" + contactperson + "\" into database", sqle);
        }
        finally
        {
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    public static void changeContactPersonDetails(DBTransaction dbTransaction, int customerId, String contactperson,
                                                  String phoneNumber, String cellNumber, String fax, String email,
                                                  boolean salesAckMail, boolean shipAlerts, boolean proformaInvoice,
                                                  boolean packingSlip, boolean commerInvoice, String address, String contactPersonValue, String oldContactPerson, boolean quoteAckMail, boolean rmaAckMail ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            if( "Customer".equalsIgnoreCase( contactPersonValue.trim() ) )
            {
                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.customer.changeCustomerContactPersonDetails" ) );            
            }
            else if( "Address".equalsIgnoreCase( contactPersonValue.trim() ) )
            {
                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.customer.changeAddressContactPersonDetails" ) );
            }

            pstmt.setString ( 1 , (contactperson   != null   ? contactperson.trim() : "") );
            pstmt.setString ( 2 , (phoneNumber     != null   ? phoneNumber.trim()   : "") );
            pstmt.setString ( 3 , (cellNumber      != null   ? cellNumber.trim()    : "") );
            pstmt.setString ( 4 , (fax             != null   ? fax.trim()           : "") );
            pstmt.setString ( 5 , (email           != null   ? email.trim()         : "") );
            pstmt.setString ( 6 , (address         != null   ? address.trim()       : "") );
            pstmt.setInt    ( 7,  (salesAckMail    == false) ? 1 : 0 );
            pstmt.setInt    ( 8,  (shipAlerts      == false) ? 1 : 0 );
            pstmt.setInt    ( 9,  (proformaInvoice == false) ? 1 : 0 );
            pstmt.setInt    ( 10,  (packingSlip     == false) ? 1 : 0 );
            pstmt.setInt    ( 11, (commerInvoice   == false) ? 1 : 0 );
            pstmt.setInt    ( 12, (quoteAckMail   == false) ? 1 : 0 );
            pstmt.setInt    ( 13, (rmaAckMail   == false) ? 1 : 0 );
            pstmt.setString ( 14, oldContactPerson );
            pstmt.setInt    ( 15, customerId );

            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not insert contactperson, \"" + contactperson + "\" into database", sqle);
        }
        finally
        {
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }


    public static boolean isContactPersonExists( String contactPerson, String contactPersonValue, int customerId, String oldContactPerson ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        int i = 1;
        int deactivated = 0;
        boolean isContactPersonExist = false;
        try
        {
            conn = DataSource.getConnection();
            if( "Customer".equals( contactPersonValue.trim() ) )
            {
                pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.checkCustomerContactPerson" ) );
            }
            else if( "Address".equals( contactPersonValue.trim() ) )
            {
                pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.checkAddressContactPerson" ) );
            }
            pstmt.setString( 1, oldContactPerson );
            pstmt.setInt( 2, deactivated );
            pstmt.setInt( 3, customerId );
            pstmt.setString( 4, contactPerson );

            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                isContactPersonExist = true;
            }
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not check Contact Person Name from database", sqle );
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return isContactPersonExist;
    }


    // Added by Sarada on 5th March 2004 to update customer id in customer_persons_table
    public static void updateContactPerson (DBTransaction dbTransaction, int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;


        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.updateContactPerson"));
            pstmt.setInt ( 1, customerId );
            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
        throw new ScalarException ("Could not update contactperson with , \"" + customerId + "\" into database", sqle);
        }
        finally
        {
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    public static void updateCustomerContactPerson( DBTransaction dbTransaction, int customerId, int rdmCustomerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.updateCustomerContactPerson" ) );
            pstmt.setInt( 1, customerId );
            pstmt.setInt( 2, rdmCustomerId );
            pstmt.executeUpdate();
            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not update contactperson with , \"" + customerId + "\" into database", sqle );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true ) conn.commit();
                        else conn.rollback();
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
    }

    public static void updateAddressContactPerson( DBTransaction dbTransaction, int addressId, int no ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.updateAddressContactPerson" ) );
            pstmt.setInt( 1, addressId );
            pstmt.setInt( 2, no );
            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not update contactperson with , \"" + addressId + "\" into database", sqle );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true ) conn.commit();
                        else conn.rollback();
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
    }

    /**
    * Get All Representative Offices
    *
    * @return repOfficesList
    */
    public static ArrayList getAllRepOffices() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList repOfficesList = new ArrayList();
        try
        {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select DISTINCT Rep_Office_Name from Rep_Offices " );

            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                repOfficesList.add( rs.getString( "Rep_Office_Name" ) );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Rep Offices from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return repOfficesList;
    }


    public static ArrayList loadAllSalesPersons() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList salesList = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select DISTINCT Sales_Person_Name from Sales_Persons_Table  " );

            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                salesList.add( rs.getString( "Sales_Person_Name" ) );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Sales Persons from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return salesList;
    }

    /**
    * Get All ShipingMethods
    *
    * @return shippingMethods
    */
    public static ArrayList loadAllShippingMethods() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList shippingList = new ArrayList();


        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("select DISTINCT Shipping_Method from Shipping_Methods_Table");

            rs = pstmt.executeQuery();

            while(rs.next())
            {
                shippingList.add( rs.getString("Shipping_Method"));
            }

        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Shipping methods from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return shippingList;
    }

    //Added the overloaded method to accept dbtransaction
    public static ArrayList loadAllShippingMethods(int customerId) throws ScalarException
    {
        return loadAllShippingMethods( null, customerId );
    }

    //To get all shipping methods of customer
    public static ArrayList loadAllShippingMethods( DBTransaction dbTransaction, int customerId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String method = null;
        String account = null;
        ArrayList shippingList = new ArrayList();

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("select DISTINCT Shipping_Method, Shipping_Account from Shipping_Methods_Table NOLOCK where sm_customer_id= ? AND IS_DISABLED=1");
            pstmt.setInt(1,customerId);
            rs = pstmt.executeQuery();

            while(rs.next())
            {
                method = rs.getString("Shipping_Method");
                account = rs.getString("Shipping_Account");
                shippingList.add ( new org.apache.struts.util.LabelValueBean (method+"   #   "+account,method+"   #   "+account) );
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Shipping methods from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return shippingList;
    }

    public static ArrayList loadShippingMethodsByCustomerId (int customerId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList shippingList = new ArrayList();
        ArrayList shippingLabelValueList = new ArrayList();


        try {
            conn = DataSource.getConnection();

            // Step 1
            // Load Shipping methods from Customer table
            pstmt = conn.prepareStatement ("select C_Shipping_method_1, C_act_number_1, C_Shipping_method_2, C_act_number_2, C_Shipping_method_3, C_act_number_3 from Customer where Customer_id = ?");
            pstmt.setInt (1, customerId);
            rs = pstmt.executeQuery();

            String shippingMethod = null;
            String shippingAccount = null;

            if (rs.next())
            {
                shippingMethod = rs.getString ("C_Shipping_method_1");
                shippingAccount = rs.getString ("C_act_number_1");

                if (shippingMethod != null) {
                    shippingList.add (new ShippingMethod (shippingMethod, shippingAccount));
                    shippingLabelValueList.add (new org.apache.struts.util.LabelValueBean (shippingMethod, shippingAccount));
                }

                shippingMethod = rs.getString ("C_Shipping_method_2");
                shippingAccount = rs.getString ("C_act_number_2");

                if (shippingMethod != null) {
                    shippingList.add (new ShippingMethod (shippingMethod, shippingAccount));
                    shippingLabelValueList.add (new org.apache.struts.util.LabelValueBean (shippingMethod, shippingAccount));
                }

                shippingMethod = rs.getString ("C_Shipping_method_3");
                shippingAccount = rs.getString ("C_act_number_3");

                if (shippingMethod != null) {
                    shippingList.add (new ShippingMethod (shippingMethod, shippingAccount));
                    shippingLabelValueList.add (new org.apache.struts.util.LabelValueBean (shippingMethod, shippingAccount));
                }
            }

            rs.close();
            rs = null;

            pstmt.close();
            pstmt = null;


            // Step 2
            // Load from Shipping_Methods_Table
            pstmt = conn.prepareStatement("select Shipping_Method, Shipping_Account from Shipping_Methods_Table where SM_Customer_ID = ?");
            pstmt.setInt (1, customerId);

            rs = pstmt.executeQuery();

            while(rs.next())
            {
                shippingMethod = rs.getString("Shipping_Method");
                shippingAccount = rs.getString("Shipping_Account");
                shippingList.add (new ShippingMethod (shippingMethod, shippingAccount));
                shippingLabelValueList.add (new org.apache.struts.util.LabelValueBean (shippingMethod, shippingAccount));
            }

        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Shipping methods from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return shippingLabelValueList;
   }


    public static ArrayList loadSalesOrderShippingMethods( int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList shippingList = new ArrayList();
        ArrayList shippingLabelValueList = new ArrayList();

        try
        {
            conn = DataSource.getConnection();

            // Step 2
            // Load from Shipping_Methods_Table
            String shippingMethod = null;
            String shippingAccount = null;

            pstmt = conn.prepareStatement( "select Shipping_Method, Shipping_Account from Shipping_Methods_Table where SM_Customer_ID = ? AND IS_DISABLED = 1" );
            pstmt.setInt( 1, customerId );

            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                shippingMethod = rs.getString( "Shipping_Method" );
                shippingAccount = rs.getString( "Shipping_Account" );
                shippingList.add( new ShippingMethod( shippingMethod, shippingAccount ) );
                shippingLabelValueList.add( new LabelValueBean( shippingMethod + "   #   " + shippingAccount, shippingAccount ) );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Shipping methods from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return shippingLabelValueList;
    }

    // ( When ODM is selected this method will load both customer & ODM shipping methods )

    public static ArrayList loadShippingMethodsForCustomerAndOdm( int customerId, int odmId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList shippingLabelValueList = new ArrayList();
        try
        {
            conn = DataSource.getConnection();

            String shippingMethod = null;
            String shippingAccount = null;

            shippingLabelValueList.add( new org.apache.struts.util.LabelValueBean( "---- For Customer ----", "'" ) );

            pstmt = conn.prepareStatement( "select Shipping_Method, Shipping_Account from Shipping_Methods_Table where SM_Customer_ID = ? AND IS_DISABLED=1" );
            pstmt.setInt( 1, customerId );

            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                shippingMethod = rs.getString( "Shipping_Method" );
                shippingAccount = rs.getString( "Shipping_Account" );
                shippingLabelValueList.add( new org.apache.struts.util.LabelValueBean( shippingMethod + "   #   " + shippingAccount, shippingAccount ) );
            }
            rs.close();
            rs = null;

            pstmt.close();
            pstmt = null;

            String endCustomerLabel = MessageResources.getMessageResources( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage( "label.sales.endCustomer" );
            shippingLabelValueList.add( new org.apache.struts.util.LabelValueBean( "---- For " + endCustomerLabel + "-----", "'" ) );

            pstmt = conn.prepareStatement( "select Shipping_Method, Shipping_Account from Shipping_Methods_Table where SM_Customer_ID = ? AND IS_DISABLED=1" );
            pstmt.setInt( 1, odmId );

            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                shippingMethod = rs.getString( "Shipping_Method" );
                shippingAccount = rs.getString( "Shipping_Account" );
                shippingLabelValueList.add( new org.apache.struts.util.LabelValueBean( shippingMethod + "   #   " + shippingAccount, shippingAccount ) );
            }


        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Shipping methods from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return shippingLabelValueList;
    }

    public static ArrayList loadAllVendorLocations() throws ScalarException
    {
        ArrayList locations = new ArrayList();

        Iterator locs = VendorDAO.getAllVendorLocations().iterator();

        while( locs.hasNext() )
        {
            Location location = ( Location ) locs.next();

            locations.add( new LabelValueBean( location.getVendorName(), Integer.toString( location.getLocationId() ) ) );
        }

        return locations;
    }

    //To restrict &amp; while displaying selectedLocations in Customer Details page
    public static ArrayList loadAllVendorLocationsForCustomer() throws ScalarException
    {
        ArrayList locations = new ArrayList();
        Iterator locs = getAllVendorLocationsForCustomer().iterator();

        while( locs.hasNext() )
        {
            Location location = ( Location ) locs.next();

            locations.add( new LabelValueBean( location.getVendorName(), Integer.toString( location.getLocationId() ) ) );
        }

        return locations;
    }


    //To restrict &amp; while displaying selectedLocations in Customer Details page
    public static ArrayList getAllVendorLocationsForCustomer() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList locationsList = null;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "vendor.selectAllLocationsForCustomer" ) );
            rs = pstmt.executeQuery();
            locationsList = new ArrayList();
            Location location = null;

            while( rs.next() )
            {
                location = new Location();
                location.setLocationId( rs.getInt( "Location_ID" ) );
                String locName = rs.getString( "L_Vendor_Name" );
                String locType = rs.getString( "L_Type" );
                String locType1 = "";
                String locType2 = "";
                String finalLocType = "";
                StringTokenizer st = new StringTokenizer( locType, "&" );
                try
                {
                    locType1 = st.nextToken();
                    locType2 = st.nextToken();
                    if( !"".equals( locType2 ) )
                    {
                        locType2 = locType2.substring( 4 );
                    }
                }
                catch( NoSuchElementException e )
                {
                    locType1 = locType;
                }
                if( locType2 != null && locType2.trim().length() != 0 )
                {
                    finalLocType = locType1 + " & " + locType2;
                }
                else
                {
                    finalLocType = locType1;
                }
                location.setVendorName( locName + "/" + finalLocType );
                locationsList.add( location );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Location Details from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return locationsList;
    }




   // --------------------------------------------------------- Protected Methods


   /**
    * Get All the customers or customer by search parameter
    *
    * @param sql
    * @return customer list
    */
    private static ArrayList getCustomers( String sql ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        Customer customer = null;
        ArrayList customerList = new ArrayList();
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( sql );
            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                customer = new Customer();
                customer.setCustomerId( rs.getInt( "Customer_Id" ) );
                customer.setCustomerName( rs.getString( "C_Name" ) );
                customer.setContactPerson( rs.getString( "C_Contact_Person" ) );
                customer.setRegion( rs.getString( "C_region" ) );
                customerList.add( customer );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Customers Details from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return customerList;
   }


    /**
    * Loads only customer information from database based on the customer ID
    *
    * @param customerId
    * @return customer Customer Object
    */
    public static Customer loadCustomerById(int customerId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Customer customer = null;


        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.loadCustomerById" ) );
            pstmt.setInt (1, customerId);

            rs = pstmt.executeQuery();

            if (rs.next() == false)
            {
                return null;
            }

            customer = new Customer (rs.getInt ("Customer_ID"),
                                      rs.getString ("C_Name"),
                                      rs.getString ( "C_NUMBER" ),
                                      rs.getString ("C_Rep_Office"),
                                      rs.getString ("C_Shipping_Instructions"),
                                      rs.getString ("C_Net_Terms"));
            customer.setIsDistributor( rs.getBoolean( "C_IS_DISTRIBUTOR" ) );
            customer.setIsConsignee( rs.getInt ( "C_IS_CONSIGNEE" ) == 0 );
            customer.setIsDefaultConsignment( rs.getInt ( "C_IS_DEFAULT_CONSIGNMENT" ) == 0 );
            customer.setIsTrackInventory( rs.getInt ( "C_IS_TRACK_INVENTORY" ) == 0 );
            customer.setRevenueType( rs.getInt ( "C_DIST_REVENUE_TYPE" ) );
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not load Customer for cusotmer ID " + customerId, sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return customer;

   }


    /**
    * Get All the customer info either to view or edit
    *
    * @param customerId
    * @return customer object
    */
   public static Customer getCustomerDetails(int customerId) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Customer customer = null;


        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.selectCustomer"));
            pstmt.setInt (1, customerId);

            rs = pstmt.executeQuery();

            if (rs.next() == false)
            {
                // Customer not found, ???Throw exception
            }
            else
            {
                customer = new Customer(rs.getInt("Customer_ID"),
                                rs.getString("C_Name"),
                                rs.getString("C_Number"),
                                rs.getString("C_Contact_Person"),
                                rs.getString("C_Shipping_Method_1"),
                                rs.getString("C_Shipping_Method_2"),
                                rs.getString("C_Shipping_Method_3"),
                                rs.getString("C_Act_Number_1"),
                                rs.getString("C_Act_Number_2"),
                                rs.getString("C_Act_Number_3"),
                                rs.getString("C_Region"),
                                rs.getString("C_Rep_Office"),
                                rs.getString("C_Sales_Person"),
                                rs.getString("C_Shipping_Instructions"),
                                rs.getInt("C_Default_Shipping_Address_Id"),
                                rs.getInt("C_Default_Billing_Address_Id"),
                                rs.getString("C_Priority"),
                                rs.getInt("C_Cancellation_Window"),
                                ((rs.getInt("C_Revision_Restriction") == 0) ? true : false),
                                ((rs.getInt("C_Location_Restriction") == 0) ? true : false),
                                ((rs.getInt("C_Datecode_Restriction") == 0) ? true : false),
                                rs.getString("C_No_Of_Datecode_Restriction"),
                                ((rs.getInt("C_Traycode_Restriction") == 0) ? true : false),
                                rs.getString("C_No_Of_Traycode_Restriction"),
                                rs.getInt("C_Least_Shippable_Qty"),
                                rs.getInt("C_Credit_Limit"),
                                rs.getString("C_Representative_Id"),
                                rs.getString("C_Representative_Name"));

                customer.setShippingRestriction((rs.getInt("C_Shipping_Restriction") == 0) ? true : false);
                customer.setNoOfShippingDaysNotEarlierThan(rs.getInt("C_NoOf_ShippingDays_NotEarlier_Than"));
                customer.setSalesRep(rs.getString("C_Sales_Rep"));
                customer.setPrintName( rs.getString("C_Print_Name") );
                customer.setSupplierNumber( rs.getString("C_Supplier_Number") );
                customer.setContractNumber( rs.getString("C_Contract_Number") );
                customer.setSpecificationNumber( rs.getString("C_Specification_Number") );
                customer.setCustomRequirements( rs.getInt("C_Custom_Requirements") == 0 ? true : false );
                customer.setEcnChangeNotification( rs.getInt("C_ECN_Change_Notification") == 0 ? true : false );
                customer.setJitWindow( rs.getInt("C_JIT_WINDOW") );
                customer.setShippingInstructionsId( rs.getInt("C_SHIPPING_INSTRUCTIONS_ID") );
				customer.setIsDistributor( rs.getBoolean( "C_Is_Distributor" ) );
                customer.setDeliveryTermsId( rs.getInt ( "C_DELIVERY_TERMS_ID" ) );
                customer.setIsConsignee( rs.getInt ( "C_IS_CONSIGNEE" ) == 0 ? true : false );
                customer.setIsDefaultConsignment( rs.getInt ( "C_IS_DEFAULT_CONSIGNMENT" ) == 0 ? true : false );
                customer.setIsTrackInventory( rs.getInt ( "C_IS_TRACK_INVENTORY") == 0 ? true : false );
                customer.setRevenueType( rs.getInt ( "C_DIST_REVENUE_TYPE" ) );
                customer.setShippingTerms( rs.getString ( "C_PAY_TERMS" ) );
				customer.setIsSample ((rs.getInt("IS_SAMPLE") == 0) ? true : false);
                customer.setDeliveryTerms( rs.getString ( "DELIVERY_TERMS" ) );                

                customer.setSelectedCustomerTypes(  CustomerDAO.loadCustomerTypeRelationShips( null, customerId ));

                Address finalAddress = getAddress(customer.getDefaultBillingAddressId());
                String customerContactPersonName = customer.getContactPerson();

                customer.setBillingAddress(finalAddress);
                customer.setShippingAddress(getAddress(customer.getDefaultShippingAddressId()));

                if( customerId > 0 )
                {
					customer.setShippingLocations(getAllAddressesOfType(customerId, "Shipping"));
					customer.setBillingLocations(getAllAddressesOfType(customerId, "Billing"));
                }
                else
                {
                	ArrayList shippingLocs = new ArrayList();
					customer.setShippingLocations(shippingLocs);
                    customer.setBillingLocations(new ArrayList());
                }


                customer.setSelectedLocations(getLocationRestrictions(customerId));

                String cperson =  customer.getContactPerson();
                ContactPerson customerContactPerson = null;
                if( cperson != null && cperson.trim().length() != 0 )
                {
					customerContactPerson = getContactPersonDetails (customerId , cperson);
                     if(customerContactPerson == null )
                     {
						customerContactPerson  = new ContactPerson();
                     }
                }
                else if ( cperson == null || cperson.trim().length() == 0 || "-1" .equals(cperson))
                {
					customerContactPerson  = new ContactPerson();
                }
                customer.setContactPersonObject(customerContactPerson);


                String billContactPersonName = finalAddress.getContactPerson();
                ContactPerson billingContactPerson = null;
				if( billContactPersonName != null && billContactPersonName.trim().length() != 0 )
				{
					 billingContactPerson = getAddressContactPersonDetails (customer.getDefaultBillingAddressId() , billContactPersonName);
					 if(billingContactPerson == null )
					 {
						billingContactPerson  = new ContactPerson();
					 }
				}
				else if ( billContactPersonName == null || billContactPersonName.trim().length() == 0 || "-1" .equals(billContactPersonName))
				{
					billingContactPerson  = new ContactPerson();
				}
				customer.setBillingContactPersonObject(billingContactPerson);

				ContactPerson shippingContactPerson = null;
				String shipContactPersonName = getAddress(customer.getDefaultShippingAddressId()).getContactPerson();

			   if( shipContactPersonName != null && shipContactPersonName.trim().length() != 0 )
			   {
					shippingContactPerson = getAddressContactPersonDetails (customer.getDefaultShippingAddressId() , shipContactPersonName);
					if(shippingContactPerson == null )
					{
						shippingContactPerson  = new ContactPerson();
					}
			   }
			   else if ( shipContactPersonName == null || shipContactPersonName.trim().length() == 0 || "-1" .equals(shipContactPersonName))
			   {
					shippingContactPerson  = new ContactPerson();
			   }
			   customer.setShippingContactPersonObject(shippingContactPerson);

               setAddressContactPersonObject(customer.getShippingLocations());
               setAddressContactPersonObject(customer.getBillingLocations());

                loadCustomerHolds( null, customer, false );
                loadCustomerHolds( null, customer, true );
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Customer Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return customer;

   }


    /**
    * Delete customer from the customer list
    *
    * @param customerId
    */
    public static void deleteCustomer(int customerId) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;


        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.customer.deleteCustomer"));
            pstmt.setInt(1,customerId);

            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not disabled Customer from database", sqlex);
        }
        finally
        {
            try
            {
                if (conn != null)
                {
                    if (success == true)
                        conn.commit();
                    else
                        conn.rollback();
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
			if ( conn != null )
			{
				try { conn.close(); } catch ( SQLException ignore ){}
			}
        }

   }


   /**
    * Get the Address
    *
    * @param addressId
    * @return Address
    */
    public static Address getAddress( int addressId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Address address = null;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.getAddress" ) );
            pstmt.setInt( 1, addressId );

            rs = pstmt.executeQuery();

            if( rs.next() == false )
            {
                // Address not found, ???Throw exception
            }
            else
            {
                address = new Address( rs.getInt( "Address_ID" ),
                       rs.getString( "Address_1" ),
                       rs.getString( "Address_2" ),
                       rs.getString( "City" ),
                       rs.getString( "State" ),
                       rs.getString( "ZipCode" ),
                       rs.getString( "Country" ),
                       rs.getString( "Phone_Number" ),
                       rs.getString( "Fax_Number" ),
                       rs.getString( "Email" ),
                       rs.getString( "Contact_Person" ),
                       rs.getString( "Cell_Number" ) );
                address.setAddress3( rs.getString( "Address_3" ) );
                address.setAddress4( rs.getString( "Address_4" ) );
                address.setEpicoreShipCode( rs.getString( "Epicore_Ship_Code" ) );
                address.setTagName(rs.getString("TAG_NAME"));
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Address from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        if( address != null )
        {
            address.setContactPersons( loadContactPersons( addressId ) );
        }
        
        return address;
    }

    public static ArrayList loadContactPersons( int addressId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList contactPersons = new ArrayList();
        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.getContactPersons" ) );
            pstmt.setInt( 1, addressId );

            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                String cperson = rs.getString( "Contact_Person" );
                contactPersons.add( cperson );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Address from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return contactPersons;
    }


    //Added by Sarada to get sales order ph,fa,email details from sales_Order_Address table if contact person is not there
    public static Address getSalesOrderAddress(int salesOrderId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Address address = null;


        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.getSalesOrderAddress"));
            pstmt.setInt (1, salesOrderId);

            rs = pstmt.executeQuery();

            if (rs.next() == false)
            {
                // Address not found, ???Throw exception
            }
            else
            {
                address = new Address();
                address.setPhoneNumber(rs.getString("Phone_Number"));
                address.setFaxNumber(rs.getString("Fax_Number"));
                address.setEmail(rs.getString("Email"));
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Address from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return address;
    }


   /**
    * Get the ShippingAddressess
    *
    * @param customerId
    * @param addressType "Shipping" or "Billing"
    * @return addressList
    */
    public static ArrayList getAllAddressesOfType(int customerId, String addressType) throws ScalarException
    {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       ArrayList shippingAddresses = new ArrayList();


       try
       {
           conn = DataSource.getConnection();
           if("Shipping".equalsIgnoreCase(addressType)) {
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.getShippingAddresses" ) );
           } else {
               pstmt = conn.prepareStatement("SELECT Address_ID FROM CUSTOMER_BILLING_ADDRESS_TABLE WHERE Customer_ID = ?");
           }

           pstmt.setInt( 1, customerId );

           rs = pstmt.executeQuery();

           Address address = null;

           while( rs.next() )
           {
               address = getAddress( rs.getInt( "address_id" ) );
               shippingAddresses.add( address );
           }
       }
       catch( Exception sqlex )
       {
           sqlex.printStackTrace();
           throw new ScalarException( "Could not get Shipping Addresses from database", sqlex );
       }
       finally
       {
           if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
       }

       if( shippingAddresses.size() == 0 ) shippingAddresses = new ArrayList( 0 );

       return shippingAddresses;

    }

    /**
     *
     * @param customerId
     * @param addressType "Shipping" or "Billing"
     * @return
     * @throws ScalarException
     */
    public static HashMap getAllContactPersonsOfType(int customerId, String addressType) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        HashMap allContactPersons = new HashMap();

        try
        {
            conn = DataSource.getConnection();
            if("Shipping".equalsIgnoreCase(addressType)) {
                pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.getShippingAddresses" ) );
            } else { //Billing
                pstmt = conn.prepareStatement( "SELECT Address_ID FROM CUSTOMER_BILLING_ADDRESS_TABLE WHERE Customer_ID = ?" );
            }

            pstmt.setInt( 1, customerId );

            rs = pstmt.executeQuery();

            HashMap contactPersons = null;

            while( rs.next() )
            {
                int addressId = rs.getInt( "address_id" );
                contactPersons = getAddressContactPersonDetailsMap( addressId );
                allContactPersons.put( "" + addressId, contactPersons );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Addresses Contact Persons from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        if( allContactPersons.size() == 0 ) allContactPersons = new HashMap( 0 );

        return allContactPersons;

    }


    /**
    * Get the getLocationRestrictions
    *
    * @param customerId
    * @return locationsList
    */
   public static String[] getLocationRestrictions( int customerId ) throws ScalarException
   {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       ArrayList locations = new ArrayList();


       try
       {
           conn = DataSource.getConnection();

           pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.getLocationRestrictions" ) );
           pstmt.setInt( 1, customerId );

           rs = pstmt.executeQuery();

           while( rs.next() )
           {
               locations.add( rs.getString( "Location_ID" ) );
           }
       }
       catch( Exception sqlex )
       {
           sqlex.printStackTrace();
           throw new ScalarException( "Could not get Locations Restrictions from database", sqlex );
       }
       finally
       {
           if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
       }

       if( locations.size() == 0 ) locations = new ArrayList( 0 );
       String[] locationsStr = new String[locations.size()];
       for( int i = 0; i < locations.size(); i++ )
       {
           locationsStr[i] = ( String ) locations.get( i );
       }

       return locationsStr;

   }


    /**
    * Save Address,
    * @param addressId , Address_1, Address_2, City, State, ZipCode, Country
     * @param insert or update
     * @param tagName
     *
    */

   public static int insertAddress(DBTransaction dbTransaction, int addressId,
                                   String address1, String address2, String address3, String address4, String city, String state, String zipCode,
                                   String country, String phone, String fax, String email, String contactPerson, String cellNumber, boolean insert, String tagName)
                    throws ScalarException
    {




        return insertAddress( dbTransaction, addressId, address1, address2, address3, address4, city, state, zipCode, country, phone, fax, email, contactPerson, cellNumber, insert, "", null, "SHIP1", tagName);

    }

    //Added by rohan on May 13th 2004 for inserting the epicore ship code.
    /**
     * This overloaded method will accept the epicore shipping code also as a parameter and inserts it in the address table.
     * This overloaded method is used when sales is integrated with epicore and other wise it can be used by passing the
     * shipping address code as empty.
     *
     * @param dbTransaction
     * @param addressId
     * @param address1
     * @param address2
     * @param address3
     * @param address4
     * @param city
     * @param state
     * @param zipCode
     * @param country
     * @param phone
     * @param fax
     * @param email
     * @param contactPerson
     * @param cellNumber
     * @param insert
     * @param customerNumber
     * @param epicoreShipCode
     * @param addressType Possible values to this param are BILLING, SHIPPING, SHIP1
     * @param tagName
     * @return
     * @throws ScalarException
     */
    public static int insertAddress(DBTransaction dbTransaction, int addressId,
                                    String address1, String address2, String address3, String address4, String city, String state, String zipCode,
                                    String country, String phone, String fax, String email, String contactPerson, String cellNumber, boolean insert, String customerNumber, String epicoreShipCode, String addressType, String tagName)
                    throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        boolean success         = false;



        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            if( insert )
            {
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.insertAddress"));
            }
            else
            {
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.updateAddress"));
            }

            pstmt.setString (1, (address1         != null ) ? address1.trim()        : "" );
            pstmt.setString (2, (address2         != null ) ? address2.trim()        : "" );
            pstmt.setString (3, (city             != null ) ? city.trim()            : "" );
            pstmt.setString (4, (state            != null ) ? state.trim()           : "" );
            pstmt.setString (5, (zipCode          != null ) ? zipCode.trim()         : "" );
            pstmt.setString (6, (country          != null ) ? country.trim()         : "" );
            pstmt.setString (7, (phone            != null ) ? phone.trim()           : "" );
            pstmt.setString (8, (fax              != null ) ? fax.trim()             : "" );
            pstmt.setString (9, (email            != null ) ? email.trim()           : "" );
            pstmt.setString (10, (contactPerson   != null ) ? contactPerson.trim()   : "" );
            pstmt.setString (11, (cellNumber      != null ) ? cellNumber.trim()      : "" );
            pstmt.setString (12, (address3        != null ) ? address3.trim()        : "" );        
            pstmt.setString (13, (address4        != null ) ? address4.trim()        : "" );
            pstmt.setString (14, (epicoreShipCode != null ) ? epicoreShipCode.trim() : "" );
            pstmt.setString (15, (tagName != null ) ? tagName.trim() : "" );

            if( !insert )
            {
                pstmt.setInt (16, addressId);
                pstmt.executeUpdate();
            }
            else
            {
                pstmt.execute();

                if( pstmt != null )
                {
                    pstmt.close();
                    pstmt = null;
                }

                pstmt = conn.prepareStatement("SELECT @@IDENTITY as address_id");
                rs = pstmt.executeQuery();

                if( rs.next() )
                {
                    addressId = rs.getInt("address_id");
                }
            }

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update Address into database", sqle);
        }
        finally
        {
            if (dbTransaction == null)
            {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return addressId;

    }

   /**
    * Save Customer_Shipping_Address_Table
    * @param addressId , Customer_ID
    * @param addressType "Shipping" or "Billing"
    *
    */
    public static void updateCustomerAddressTable(DBTransaction dbTransaction, int customerId,
                                                  int addressId, String addressType) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;


        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.insertShippingAddressTable"));

            pstmt.setInt (1, customerId);
            pstmt.setInt (2, addressId);
            pstmt.setString(3, addressType);

            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update customer shipping/Billing Address table into database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

    }



    public static void insertRepOffice (DBTransaction dbTransaction, String repoffice) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;


        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.insertRepOffice"));

            pstmt.setString ( 1 , repoffice );
            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not insert rep office, \"" + repoffice + "\" into database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

    }
    public static void insertRepOffice (DBTransaction dbTransaction, String repoffice, int customerId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;


        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.insertNewRepOffice"));
            // ADDED trim() By Suresh Reddy on 02 JUNE 2004
            pstmt.setString ( 1 , repoffice.trim() );
            pstmt.setInt ( 2 , customerId );
            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not insert rep office, \"" + repoffice + "\" into database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

    }

    public static void updateRepOffice( DBTransaction dbTransaction, int customerId, int rdmCustomerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.updateRepOffice" ) );
            pstmt.setInt( 1, customerId );
            pstmt.setInt( 2, rdmCustomerId );
            pstmt.executeUpdate();
            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not update  rep office with , \"" + customerId + "\" into database", sqle );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
    }

    public static void insertRegion( DBTransaction dbTransaction, String region ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.insertRegion" ) );
            pstmt.setString( 1, region.trim() );
            pstmt.execute();
            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not insert region, \"" + region + "\" into database", sqle );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

    }

    public static void updateRegion( DBTransaction dbTransaction, int customerId, int rdmCustomerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.updateRegion" ) );
            pstmt.setInt( 1, customerId );
            pstmt.setInt( 2, rdmCustomerId );
            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not update regions with , \"" + customerId + "\" into database", sqle );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

    }

    public static void insertSalesPerson( DBTransaction dbTransaction, String salesperson, int customerId, int addressId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.insertSalesPerson" ) );

            pstmt.setString( 1, salesperson );
            pstmt.setInt( 2, customerId );
            pstmt.setInt( 3, addressId );
            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not insert salesperson, \"" + salesperson + "\" into database", sqle );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

    }

    public static void updateSalesPerson( DBTransaction dbTransaction, String salesperson, int customerId, int addressId, String oldsalesperson ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.updateSalesPersonDetials" ) );

            pstmt.setString( 1, salesperson );
            pstmt.setInt( 2, addressId );
            pstmt.setInt( 3, customerId );
            pstmt.setString( 4, oldsalesperson );
            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not insert salesperson, \"" + salesperson + "\" into database", sqle );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true ) conn.commit();
                        else conn.rollback();
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
    }

    public static void insertSalesRep( DBTransaction dbTransaction, String salesrep, int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.insertSalesRep" ) );

            pstmt.setString( 1, salesrep );
            pstmt.setInt( 2, customerId );
            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not insert salesrep, \"" + salesrep + "\" into database", sqle );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

    }

    public static void updateSalesPerson( DBTransaction dbTransaction, int customerId, int rdmCustomerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( "Update Customer_SalesPerson_Relationship set Customer_Id = ? where Customer_Id = ? " );
            pstmt.setInt( 1, customerId );
            pstmt.setInt( 2, rdmCustomerId );
            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not update salesperson with , \"" + customerId + "\" into database", sqle );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true ) conn.commit();
                        else conn.rollback();
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

    }

    public static void updateSalesRep( DBTransaction dbTransaction, int customerId, int rdmCustomerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.updateSalesRep" ) );
            pstmt.setInt( 1, customerId );
            pstmt.setInt( 2, rdmCustomerId );
            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not update salesrep with , \"" + customerId + "\" into database", sqle );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

    }


    /**
    * Save the Customer details
    *
    * @param customer
    * @return customerId
    */
    private static Customer saveCustomer( DBTransaction dbTransaction, Customer customer,  boolean insert ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        int customerId          = -1;
        boolean success         = false;
        String shippingMethod   = null;
        String shippingAccount  = null;
        try 
		{
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            String contactperson = customer.getContactPerson();

            if( "-1".equals( contactperson ) )
            {
                contactperson = "";
            }
            if( ( customer.getNewShippingMethod1() != null && customer.getNewShippingMethod1().trim().length() > 0 ) )
            {
                shippingMethod  = customer.getNewShippingMethod1();
                shippingAccount = customer.getNewShippingAccount1();
            }
            else
            {
                if( customer.getShippingMethod1() != null )
                {
                    int index = customer.getShippingMethod1().indexOf ( "   #" );
                    if ( ( index != -1 ) && ( index != 0 ) )
                    {
                        shippingMethod  = customer.getShippingMethod1().substring ( 0, index );
                        if( customer.getShippingMethod1().length() > index + 7 )
                        {
                            shippingAccount = customer.getShippingMethod1().substring ( index + 7, customer.getShippingMethod1().length() );
                        }
                    }
                    else
                    {
                        shippingMethod  = customer.getShippingMethod1();
                        shippingAccount = customer.getShippingAccount1();
                    }
                }
            }

            //Shipping Method is -1, assigning empty value.
            if ( shippingMethod != null && shippingMethod.trim().length() > 0 && shippingMethod.equalsIgnoreCase("-1") )
            {
                shippingMethod = "";
                shippingAccount = "";
            }
            if(insert)
            {
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.insertCustomer"));
                pstmt.setString (1, customer.getCustomerName());

                pstmt.setString (2, contactperson);
                pstmt.setString (3, shippingMethod);
                pstmt.setString (4, customer.getShippingMethod2());
                pstmt.setString (5, customer.getShippingMethod3());
                pstmt.setString (6, shippingAccount);
			}
            else
            {
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.updateCustomer"));
                pstmt.setString (1, customer.getCustomerName());
                pstmt.setString (2, contactperson);
                pstmt.setString (3, shippingMethod);
                pstmt.setString (4, customer.getShippingMethod2());
                pstmt.setString (5, customer.getShippingMethod3());
                pstmt.setString (6, shippingAccount);
			}

			pstmt.setString (7, customer.getShippingAccount2());
			pstmt.setString (8, customer.getShippingAccount3());
			pstmt.setString (9, ((customer.getNewRegion() != null) && (customer.getNewRegion().length() > 0)) ? customer.getNewRegion() : customer.getRegion());
			pstmt.setString (10, ((customer.getNewRepOffice() != null) && (customer.getNewRepOffice().length() > 0)) ? customer.getNewRepOffice() : customer.getRepOffice());
			pstmt.setString (11, ((customer.getNewRepOffice() != null) && (customer.getNewSalesPerson().length() > 0))  ? customer.getNewSalesPerson() : customer.getSalesPerson());
			pstmt.setString (12, customer.getShippingInstructions());
			pstmt.setInt (13, customer.getDefaultShippingAddressId());
			pstmt.setInt (14, customer.getDefaultBillingAddressId());
			pstmt.setString (15, customer.getPriority());
			pstmt.setInt (16, customer.getCancellationWindow());
			pstmt.setInt (17, customer.getRevisionRestriction() ? 0 : 1);
			pstmt.setInt (18, customer.getLocationRestriction() ? 0 : 1);
			pstmt.setInt (19, customer.getDateCodeRestriction() ? 0 : 1);
			pstmt.setString (20, customer.getDateCodeRestriction() ? customer.getNoOfDateCodeRestrictions() : null);
			pstmt.setInt (21, customer.getTrayCodeRestriction() ? 0 : 1);
			pstmt.setString (22, customer.getTrayCodeRestriction() ? customer.getNoOfTrayCodeRestrictions() : null);
			pstmt.setInt (23, customer.getLeastShippableQty());
			pstmt.setInt (24, customer.getCreditLimit());
			pstmt.setString (25, customer.getRepId());
			pstmt.setString (26, customer.getRepName());
			pstmt.setInt (27, customer.getShippingRestriction() ? 0 : 1);
			pstmt.setInt (28, customer.getShippingRestriction() ? customer.getNoOfShippingDaysNotEarlierThan() : 0);
			pstmt.setString (29, ((customer.getNewSalesRep() != null) && (customer.getNewSalesRep().length() > 0)) ? customer.getNewSalesRep() : customer.getSalesRep());

			pstmt.setString(30, customer.getPrintName());
			pstmt.setString(31, customer.getSupplierNumber());
			pstmt.setString(32, customer.getContractNumber());
			pstmt.setString(33, customer.getSpecificationNumber());
			pstmt.setInt(34, customer.getCustomRequirements() ? 0 : 1 );
			pstmt.setInt(35, customer.getEcnChangeNotification() ? 0 : 1 );
            pstmt.setString(36, customer.getShippingTerms());

            if(!insert) 
			{
				pstmt.setString (37, customer.getCustomerNumber());
				pstmt.setInt(38,customer.getJitWindow());
				pstmt.setString(39, customer.getShippingInstructionsId() > 0 ? customer.getShippingInstructionsId()+"" : null );
				pstmt.setBoolean( 40, customer.getIsDistributor() );
                pstmt.setString ( 41, customer.getDeliveryTermsId() > 0 ? customer.getDeliveryTermsId()+"" : null );
                pstmt.setInt (42, customer.getIsConsignee() ? 0 : 1);
                pstmt.setInt (43, customer.getIsDefaultConsignment() ? 0 : 1);
                pstmt.setInt ( 44, customer.getIsTrackInventory() ? 0 : 1 );
                pstmt.setInt ( 45, customer.getRevenueType() );
                pstmt.setInt (46, customer.getIsSample() ? 0 : 1);
                pstmt.setInt (47, customer.getCustomerId());


                pstmt.executeUpdate();
            } 
			else 
			{
				pstmt.setInt (37, 1);
				pstmt.setString (38, customer.getCustomerNumber());
				pstmt.setInt(39,customer.getJitWindow());
				pstmt.setString(40, customer.getShippingInstructionsId() > 0 ? customer.getShippingInstructionsId()+"" : null );
				pstmt.setBoolean( 41, customer.getIsDistributor() );
                pstmt.setString ( 42, customer.getDeliveryTermsId() > 0 ? customer.getDeliveryTermsId()+"" : null );
                pstmt.setInt (43, customer.getIsConsignee() ? 0 : 1);
                pstmt.setInt (44, customer.getIsDefaultConsignment() ? 0 : 1);
                pstmt.setInt ( 45, customer.getIsTrackInventory() ? 0 : 1 );
                pstmt.setInt ( 46, customer.getRevenueType() );
                pstmt.setInt (47, customer.getIsSample() ? 0 : 1);
                pstmt.executeUpdate();

                pstmt.close();
				pstmt = null;

				pstmt = conn.prepareStatement("SELECT @@IDENTITY as customer_id");
				rs = pstmt.executeQuery();
				if (rs.next())
					  customerId = rs.getInt("customer_id");
				customer.setCustomerId(customerId);
			}
                success = true;
		}
		catch (Exception sqle) {
			sqle.printStackTrace();
			throw new ScalarException ("Could not update Customer into database", sqle);
		}
		finally
		{
			if (dbTransaction == null)
			{
				try
				{
					if (conn != null)
					{
						if (success == true)
							conn.commit();
						else
							conn.rollback();
					}
				}
				catch (SQLException ignore) {}
			}
			if (rs != null) {
				try { rs.close(); } catch (SQLException ignore){}
			}
            if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}

		return customer;
    }

    /**
     * Save the locations
     * @param customerId
     */
    private static void saveLocationRestrictions(DBTransaction dbTransaction, int customerId,
                        String[] locations, boolean locationRestriction) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;


        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            //Delete all the location restriction Ids
            pstmt = conn.prepareStatement(SqlQueryMappings.get("sales.customer.deleteSelectedLocations"));
            pstmt.setInt(1, customerId);

            pstmt.executeUpdate();

            pstmt.close();
            pstmt = null;

            if(locationRestriction) {
                if ((locations != null) && (locations.length != 0)) {
                     //Insert the selected locations again...
                     pstmt = conn.prepareStatement(SqlQueryMappings.get("sales.customer.saveSelectedLocations"));

                    for (int i=0; i<locations.length; i++)
                    {
                        pstmt.setInt(1, customerId);
                        pstmt.setInt(2, Integer.parseInt(locations[i]));

                        pstmt.addBatch();
                    }

                    pstmt.executeBatch();
                }
            }

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update selected locations into database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }



    /**
    * Save customer, Add the customer if not exists Or Update the customer if exists
    *
    */
    public static int saveCustomerDetails( DBTransaction dbTransaction, Customer customer ) throws ScalarException
    {
        int customerId  = -1;
        int addressId   = -1;
        boolean success = false;


        try
        {

            //if customerId == -1 then vendor is not exists insert the data, if exists then update it
            if( customer.getCustomerId() < 0 )
            {
                if( customer == null )
                {
                    customer = new Customer();
                }

                // Set the contact person to null if it equals -1 or ""
                if( "-1".equals( customer.getBillingAddress().getContactPerson()) || "".equals( customer.getBillingAddress().getContactPerson() ) )
                {
                    customer.getBillingAddress().setContactPerson(null);
                }



                //Insert & Set the default billing address
                customer.setDefaultBillingAddressId(insertAddress(dbTransaction,
                                             customer.getBillingAddress().getAddressId(),
                                             customer.getBillingAddress().getAddress1(),
                                             customer.getBillingAddress().getAddress2(),
                                             customer.getBillingAddress().getAddress3(),
                                             customer.getBillingAddress().getAddress4(),
                                             customer.getBillingAddress().getCity(),
                                             customer.getBillingAddress().getState(),
                                             customer.getBillingAddress().getZipCode(),
                                             customer.getBillingAddress().getCountry(),
                                             customer.getBillingAddress().getPhoneNumber(),//customer.getBillingAddress().getPhoneNumber(),
                                             customer.getBillingAddress().getFaxNumber(),//customer.getBillingAddress().getFaxNumber(),
                                             customer.getBillingAddress().getEmail(),
                                             customer.getBillingAddress().getContactPerson(),
                                             customer.getBillingAddress().getCellNumber(),
                                             true, customer.getCustomerNumber(),customer.getBillingAddress().getEpicoreShipCode(), "BILLING", customer.getBillingAddress().getTagName()) );




                //Set the contact person to null if it equals -1 or ""
                if( "-1".equals( customer.getShippingAddress().getContactPerson()) || "".equals( customer.getShippingAddress().getContactPerson() ) )
                {
                    customer.getShippingAddress().setContactPerson(null);
                }


                customer.setDefaultShippingAddressId(insertAddress(dbTransaction,
                                            customer.getShippingAddress().getAddressId(),
                                            customer.getShippingAddress().getAddress1(),
                                            customer.getShippingAddress().getAddress2(),
                                            customer.getShippingAddress().getAddress3(),
                                            customer.getShippingAddress().getAddress4(),
                                            customer.getShippingAddress().getCity(),
                                            customer.getShippingAddress().getState(),
                                            customer.getShippingAddress().getZipCode(),
                                            customer.getShippingAddress().getCountry(),
                                            customer.getShippingContactPersonObject().getPhoneNumber(),
                                            customer.getShippingContactPersonObject().getFaxNumber(),
                                            customer.getShippingContactPersonObject().getEmail(),
                                            customer.getShippingAddress().getContactPerson(),
                                            customer.getShippingContactPersonObject().getCellNumber(),
                                            true, customer.getCustomerNumber(),customer.getShippingAddress().getEpicoreShipCode(), "SHIPPING", customer.getShippingAddress().getTagName()) );


                //Insert the new Region, RepOffice, SalesPerson
                insertAllNew( dbTransaction, customer.getNewRegion(), customer.getNewRepOffice(), customer.getNewSalesPerson(),customer.getNewSalesRep() );
                
                //Insert the customer details then return customerId, boolean true for insert
                customer = saveCustomer( dbTransaction, customer, true);
                ArrayList shippingAdds = customer.getShippingLocations();
                updateAddresses(dbTransaction, shippingAdds, customer.getCustomerId(), "Shipping");

                ArrayList billingAddresses = customer.getBillingLocations();
                updateAddresses(dbTransaction, billingAddresses, customer.getCustomerId(), "Billing");

                //Set the location restrictios
                if( customer.getLocationRestriction() )
                {
                    saveLocationRestrictions(dbTransaction,customer.getCustomerId(),
                    customer.getSelectedLocations(),customer.getLocationRestriction());
                }

            }
            else
            {
                //Insert the new Region, RepOffice, SalesPerson
                //-- Set the contact person to null if it equals -1 or ""
                if( "-1".equals( customer.getBillingAddress().getContactPerson() ) || "".equals( customer.getBillingAddress().getContactPerson() ) )
                {
                    customer.getBillingAddress().setContactPerson( null );
                }

                // Set the contact person to null if it equals -1 or ""
                if("-1".equals(customer.getShippingAddress().getContactPerson()) || "".equals(customer.getShippingAddress().getContactPerson()) )
                {
                    customer.getShippingAddress().setContactPerson(null);
                }

                insertAllNew(dbTransaction,customer.getNewRegion(),customer.getNewRepOffice(),customer.getNewSalesPerson(),customer.getNewSalesRep() );
                //customer.setContactPerson(customer.getBillingAddress().getContactPerson());   //Added by sarada to set contact person
                customer = saveCustomer(dbTransaction, customer, false);

                addressId = insertAddress(dbTransaction,
                                customer.getDefaultBillingAddressId(),
                                customer.getBillingAddress().getAddress1(),
                                customer.getBillingAddress().getAddress2(),
                                customer.getBillingAddress().getAddress3(),
                                customer.getBillingAddress().getAddress4(),
                                customer.getBillingAddress().getCity(),
                                customer.getBillingAddress().getState(),
                                customer.getBillingAddress().getZipCode(),
                                customer.getBillingAddress().getCountry(),
                                customer.getBillingAddress().getPhoneNumber(),
                                customer.getBillingAddress().getFaxNumber(),
                                customer.getBillingAddress().getEmail(),
                                customer.getBillingAddress().getContactPerson(),
                                customer.getBillingAddress().getCellNumber(),
                                false, customer.getCustomerNumber(),customer.getBillingAddress().getEpicoreShipCode(), "SHIP1", customer.getBillingAddress().getTagName());


                //This condition will arise when epicore is integrated where we are setting
                //the shipping address as the billing address for the first time
                if( customer.getDefaultBillingAddressId() != customer.getDefaultShippingAddressId() )
                {
                    addressId = insertAddress(dbTransaction,
                                customer.getDefaultShippingAddressId(),
                                customer.getShippingAddress().getAddress1(),
                                customer.getShippingAddress().getAddress2(),
                                customer.getShippingAddress().getAddress3(),
                                customer.getShippingAddress().getAddress4(),
                                customer.getShippingAddress().getCity(),
                                customer.getShippingAddress().getState(),
                                customer.getShippingAddress().getZipCode(),
                                customer.getShippingAddress().getCountry(),
                                customer.getShippingContactPersonObject().getPhoneNumber(),
                                customer.getShippingContactPersonObject().getFaxNumber(),
                                customer.getShippingContactPersonObject().getEmail(),
                                customer.getShippingAddress().getContactPerson(),
                                customer.getShippingContactPersonObject().getCellNumber(),
                                false, customer.getCustomerNumber(),customer.getShippingAddress().getEpicoreShipCode(), "SHIP1", customer.getShippingAddress().getTagName());
                }

                //}
                ArrayList shippingAdds = customer.getShippingLocations();
                updateAddresses(dbTransaction, shippingAdds, customer.getCustomerId(), "Shipping");

                ArrayList billingAddresses = customer.getBillingLocations();
                updateAddresses(dbTransaction, billingAddresses, customer.getCustomerId(), "Billing");

                saveLocationRestrictions(dbTransaction,customer.getCustomerId(),
                customer.getSelectedLocations(), customer.getLocationRestriction());

            }

            updateContactPersonDetails( dbTransaction, customer.getCustomerId(),customer.getContactPerson(),
                                                        customer.getContactPersonObject().getPhoneNumber(),
                                                        customer.getContactPersonObject().getCellNumber(),
                                                        customer.getContactPersonObject().getFaxNumber(),
                                                        customer.getContactPersonObject().getEmail(),
                                                        customer.getContactPersonObject().getAddress());

		    updateAddressContactPersonDetails( dbTransaction, customer.getDefaultBillingAddressId(),customer.getBillingAddress().getContactPerson(),
															customer.getBillingContactPersonObject().getPhoneNumber(),
															customer.getBillingContactPersonObject().getCellNumber(),
															customer.getBillingContactPersonObject().getFaxNumber(),
															customer.getBillingContactPersonObject().getEmail(),
															customer.getBillingContactPersonObject().getAddress());

            //This condition will arise when epicore is integrated where we are setting
            //the shipping address as the billing address for the first time
            if( customer.getDefaultBillingAddressId() != customer.getDefaultShippingAddressId() )
            {
    		    updateAddressContactPersonDetails( dbTransaction, customer.getDefaultShippingAddressId(),customer.getShippingAddress().getContactPerson(),
														customer.getShippingContactPersonObject().getPhoneNumber(),
														customer.getShippingContactPersonObject().getCellNumber(),
														customer.getShippingContactPersonObject().getFaxNumber(),
														customer.getShippingContactPersonObject().getEmail(),
														customer.getShippingContactPersonObject().getAddress());
            }
            success = true;
        }
        catch (ScalarException se)
        {
            se.printStackTrace();
        }

       return customer.getCustomerId();
    }

    public static void updateContactPersonDetails (DBTransaction dbTransaction, int customerId , String contactPerson, String phoneNumber, String cellNumber, String fax, String email , String address)throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;



        try
        {
            if (dbTransaction == null)
            {
                  conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement(SqlQueryMappings.get("sales.customer.updateCustomerContactPersonDetails"));

            pstmt.setString ( 1, (phoneNumber    != null) ? phoneNumber.trim()   : "");
            pstmt.setString ( 2, (cellNumber     != null) ? cellNumber.trim()    : "");
            pstmt.setString ( 3, (fax            != null) ? fax.trim()           : "");
            pstmt.setString ( 4, (email          != null) ? email.trim()         : "");
            pstmt.setString ( 5, (address        != null) ? address.trim()       : "");
            pstmt.setString ( 6, (contactPerson  != null) ? contactPerson.trim() : "");
            pstmt.setInt ( 7, customerId);
            pstmt.executeUpdate();
            success = true;

        }
        catch(Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update contact person info into database", sqle);
        }

        finally
        {
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

	public static void updateAddressContactPersonDetails (DBTransaction dbTransaction, int addressId , String contactPerson, String phoneNumber, String cellNumber, String fax, String email , String address)throws ScalarException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean success = false;
		try
		{
			if (dbTransaction == null)
			{
				  conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
			pstmt = conn.prepareStatement(SqlQueryMappings.get("sales.customer.updateAddressContactPersonDetails"));

			pstmt.setString ( 1, (phoneNumber    != null) ? phoneNumber.trim()   : "");
            pstmt.setString ( 2, (cellNumber     != null) ? cellNumber.trim()    : "");
            pstmt.setString ( 3, (fax            != null) ? fax.trim()           : "");
            pstmt.setString ( 4, (email          != null) ? email.trim()         : "");
            pstmt.setString ( 5, (address        != null) ? address.trim()       : "");
            pstmt.setString ( 6, (contactPerson  != null) ? contactPerson.trim() : "");
			pstmt.setInt ( 7, addressId);
			pstmt.executeUpdate();
			success = true;

		}
		catch(Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not update contact person info into database", sqle);
		}
		finally
		{
			if (dbTransaction == null)
			{
				try
				{
					if (conn != null)
					{
						if (success == true)
							conn.commit();
						else
							conn.rollback();
					}
				}
				catch (SQLException ignore) {}
			}
			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
	}


   /**
    * Insert the new Region, RepOffice and SalesPersons in the static tables
    * @param region
    * @param repOffice
    * @param salesPerson
    */
    public static void insertAllNew(DBTransaction dbTransaction, String region, String repOffice, String salesPerson, String salesRep)
                        throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall ( " { Call sp_insert_new_regions( ?, ?, ? ) } " );
            cstmt.setString( 1, region );
            cstmt.setString( 2, repOffice );
            cstmt.setString( 3, salesPerson );

            cstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not insert customer static table info into database", sqle);
        }
        finally
        {
            if (dbTransaction == null)
            {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

   }

   /**
    * Update the customer default address
    * @param customerId
    * @param addressId
    * @param addressType "Shipping" or "Billing"
    */
   public static void updateCustomerDefaultAddress(DBTransaction dbTransaction, int customerId, int addressId, String addressType)
                        throws ScalarException
   {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;


        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall (SqlQueryMappings.get ("sales.customer.updateCustomerDefaultShippindAddress"));
            cstmt.setInt (1, customerId);
            cstmt.setInt (2, addressId);
            cstmt.setString (3, addressType);
            cstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save customer Address into database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

   }


   /**
    * Remove the address from the customer list
    * @param customerId
    * @param addressId
    * @param addressType
    */
    public static void removeAddress(DBTransaction dbTransaction, int customerId, int addressId, String addressType) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            if("Shipping".equalsIgnoreCase(addressType)) {
                pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.deleteCustomerAddress" ) );
            } else {
                pstmt = conn.prepareStatement( "DELETE FROM CUSTOMER_BILLING_ADDRESS_TABLE WHERE Customer_ID = ? and Address_ID = ?" );
            }


            pstmt.setInt( 1, customerId );
            pstmt.setInt( 2, addressId );

            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not delete customer Address into database", sqle );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
    }

    /**
    * Get Customer Name
    * @param locationIds
    * @return locationNames
    */
   public static String[] selectedLocations(String[] locationIds) throws ScalarException
   {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String[] locations = new String[locationIds.length];


        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.getLocationName"));

            for(int i=0; i<locationIds.length; i++)
            {
                pstmt.setInt (1, Integer.parseInt(locationIds[i]));
                rset = pstmt.executeQuery();
                if(rset.next() != false)
                    locations[i] = rset.getString("LocationName");
            }

        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Customer Name from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
       return locations;
   }

    //To restrict &amp; while displaying selectedLocations in view/print page
    public static String[] selectedLocationsForCustomer( String[] locationIds ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String[] locName = new String[locationIds.length];
        String[] locType = new String[locationIds.length];
        String[] locType1 = new String[locationIds.length];
        String[] locType2 = new String[locationIds.length];
        String[] finalLocType = new String[locationIds.length];
        String[] finalName = new String[locationIds.length];

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.getLocationNameForCustomer" ) );

            for( int i = 0; i < locationIds.length; i++ )
            {
                pstmt.setInt( 1, Integer.parseInt( locationIds[i] ) );
                rset = pstmt.executeQuery();
                if( rset.next() != false )
                {
                    locName[i] = rset.getString( "L_Vendor_Name" );
                    locType[i] = rset.getString( "L_Type" );
                }
                StringTokenizer st = new StringTokenizer( locType[i], "&" );
                try
                {
                    locType1[i] = st.nextToken();
                    locType2[i] = st.nextToken();
                    if( !"".equals( locType2[i] ) )
                    {
                        locType2[i] = locType2[i].substring( 4 );
                    }
                }
                catch( NoSuchElementException e )
                {
                    locType1[i] = locType[i];
                }
                if( locType2[i] != null && locType2[i].trim().length() != 0 )
                {
                    finalLocType[i] = locType1[i] + " & " + locType2[i];
                }
                else
                {
                    finalLocType[i] = locType1[i];
                }
                finalName[i] = locName[i] + "/" + finalLocType[i];
            }
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not Customer Name from database", sqle );
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return finalName;
    }


    /*
    * Check for the customer name existence
    * @param customerName
    * @return boolen
    */
   public static boolean isCustomerExists(String customerName) throws ScalarException
   {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rset = null;
       int i = 1;


        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement(SqlQueryMappings.get("sales.customer.checkCustomerName"));
            pstmt.setString(1, customerName);
            //Added by Sarada on 14th June
            pstmt.setByte(2,(byte)i);

            rset = pstmt.executeQuery();

            if(rset.next())
                return true;
            else
                return false;

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not check Customer Name from database", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
   }

    public static boolean isCustomerExists( String customerName, int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean isCustomerExist = false;
        int i = 1;
        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.checkCustomerNameWithid" ) );
            pstmt.setString( 1, customerName );
            pstmt.setByte( 2, ( byte ) i );
            pstmt.setInt( 3, customerId );

            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                isCustomerExist = true;
            }
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not check Customer Name from database", sqle );
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return isCustomerExist;
    }


    /**
    * Get Customer Name
    * @param customerId
    * @return customerName
    */
    public static String loadCustomerName( int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String customerName = null;
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.getCustomerName" ) );
            pstmt.setInt( 1, customerId );
            rset = pstmt.executeQuery();
            if( rset.next() )
            {
                customerName = rset.getString( "C_Name" );
            }
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not retreive Customer Name from database", sqle );
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        if( customerName == null || customerName.trim().length() == 0 )
        {
            return null;
        }
        else
        {
            return customerName;
        }
    }

    public static boolean doesShippingMethodExist (DBTransaction dbTransaction, int customerId, String methodname, String account) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean shippingMethodExists = false;
        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            if( methodname == null )
            {
                methodname = "";
            }
            if( account == null )
            {
                account = "";
            }

            pstmt = conn.prepareStatement ("select * from shipping_methods_table where SM_Customer_id = ? and Shipping_Method=? and Shipping_Account= ?");

            pstmt.setInt   ( 1, customerId);
            pstmt.setString( 2, methodname.trim() );
            pstmt.setString( 3, account.trim());
            rs = pstmt.executeQuery();

            while (rs.next())
            {
                shippingMethodExists = true;

            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not find Method Name, \"" + methodname + "\" in the database", sqle);
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return shippingMethodExists;
    }


    public static void insertShippingMethod (DBTransaction dbTransaction, int customerId, String methodname, String account) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            if( methodname == null )
            {
                methodname = "";
            }
            if( account == null )
            {
                account = "";
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.insertShippingMethod"));

            pstmt.setInt    ( 1, customerId);
            pstmt.setString ( 2, methodname.trim());
            pstmt.setString ( 3, account.trim() );
            pstmt.executeUpdate();
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not insert Method Name, \"" + methodname + "\" into database", sqle);
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if (conn != null)
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

    }


    public static boolean doesLineItemShipped( DBTransaction dbTransaction, String salesOrderId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        int totalmethods = 0;
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( "select count(*) as total from customer_open_order_lineitem where cool_status = ? and salesorder_id = ? " );
            pstmt.setString( 1, "SHIPPED" );
            pstmt.setInt( 2, Integer.parseInt( salesOrderId.trim() ) );
            rs = pstmt.executeQuery();
            if( rs.next() )
            {
                totalmethods = rs.getInt( "total" );
            }
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "There is no Shipped LineItem in database for this SalesOrderId", sqle );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        
        if( totalmethods > 0 )
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    public static boolean doesSalesOrderExist( DBTransaction dbTransaction, int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int totalsalesorders = 0;
        boolean success = false;
        try
        {

            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.doesSalesOrderExist" ) );
            pstmt.setInt( 1, customerId );
            rs = pstmt.executeQuery();
            if( rs.next() )
            {
                totalsalesorders = rs.getInt( "total" );
            }
            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not find SAlesOrder for the corresponding customer in the database", sqle );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        if( totalsalesorders > 0 )
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    public static boolean doescheckQuotetionExist( DBTransaction dbTransaction, int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int totalQutations = 0;
        boolean success = false;
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.doesQuotetionExist" ) );
            pstmt.setInt( 1, customerId );
            rs = pstmt.executeQuery();
            if( rs.next() )
            {
                totalQutations = rs.getInt( "total" );
            }
            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not find Quotation for the corresponding customer in the database", sqle );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        if( totalQutations > 0 )
        {
            return true;
        }
        else
        {
            return false;
        }
    }


    public static void main(String[] args) throws ScalarException {
        String[] loc = {"6","15","16"};
    }

    // Added by vasanth 07/25/2003

    public static void saveCustomerDetailsForBacklog(DBTransaction dbTransaction, String salesPerson, String contactPerson,
                String phoneNumber,String faxNumber,String email,String address1, String address2, String city,
                String state, String zipcode, String country, int customerId) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;



        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.customerDetailsForBacklog"));

            pstmt.setString (1, salesPerson);
            pstmt.setString (2, contactPerson );
            pstmt.setString (3, phoneNumber);
            pstmt.setString (4, faxNumber);
            pstmt.setString (5, email);
            pstmt.setString (6, address1);
            pstmt.setString (7, address2);
            pstmt.setString (8, city);
            pstmt.setString (9, state);
            pstmt.setString(10, zipcode);
            pstmt.setString(11, country);
            pstmt.setInt (12, customerId);

            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update the customer details for backlogs into database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }



    }

    public static void saveCustomerPartNumbers( int customerid, String[] productIds, String[] customerpartnumbers, String[] labelNumbers, String[] odmIds ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        CallableStatement cstmt = null;
        boolean success = false;
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.deleteCustomerPartNumbers" ) );
            pstmt.setInt( 1, customerid );
            int cnt = pstmt.executeUpdate();

            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.insertCustomerPartNumbers" ) );
            for( int i = 0; i < productIds.length; i++ )
            {
                if( ( productIds[i] != null && productIds[i].length() != 0 ) && ( ( customerpartnumbers[i] != null && customerpartnumbers[i].length() != 0 ) || ( labelNumbers[i] != null && labelNumbers[i].length() != 0 ) ) )
                {
                    pstmt.setInt( 1, customerid );
                    pstmt.setInt( 2, Integer.parseInt( productIds[i] ) );
                    pstmt.setString( 3, customerpartnumbers[i] );
                    pstmt.setString( 4, labelNumbers[i] );
                    pstmt.setString( 5, "-1".equalsIgnoreCase( odmIds[i] ) ? null:odmIds[i] );
                    pstmt.addBatch();
                }
            }
            pstmt.executeBatch();

            pstmt.close();
            pstmt = null;

            /*if( Constants.CLIENT_NAME_AIRGO.equalsIgnoreCase( ConfigManager.getClientNameAirgo() ) )
            {
                cstmt = conn.prepareCall( "{ call sp_insert_customer_part_numbers( ? ) }" );
                cstmt.setInt( 1, customerid );
                cstmt.execute();
            }*/
            success = true;
        }
        catch( Exception se )
        {
            se.printStackTrace();
            throw new ScalarException( "Could not save part number details in database", se );
        }
        finally
        {
            try
            {
                if( success == true )
                {
                    conn.commit();
                }
                else
                {
                    conn.rollback();
                }
            }
            catch( SQLException ignore )
            {
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
    }

    public static ContactPerson getContactPersonDetails( int customerId, String contactPerson )
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        ContactPerson contactperson = null;
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.getCustomerContactPersonDetails" ) );

            pstmt.setInt( 1, customerId );
            pstmt.setString( 2, contactPerson );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                contactperson = new ContactPerson();
                contactperson.setContactPerson( rs.getString( "Contact_Person" ) );
                contactperson.setPhoneNumber( rs.getString( "Phone_Number" ) );
                contactperson.setCellNumber( rs.getString( "Cell_Number" ) );
                contactperson.setFaxNumber( rs.getString( "Fax" ) );
                contactperson.setEmail( rs.getString( "Email" ) );
                contactperson.setAddress( rs.getString( "Address" ) );
            }
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                if( success == true )
                {
                    conn.commit();
                }
                else
                {
                    conn.rollback();
                }
            }
            catch( SQLException ignore )
            {
            }

            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return contactperson;

    }

    public static ContactPerson getAddressContactPersonDetails( int billingAddressId, String contactPerson )
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        ContactPerson contactperson = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.getAddressContactPersonDetails" ) );

            pstmt.setInt( 1, billingAddressId );
            pstmt.setString( 2, contactPerson );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                contactperson = new ContactPerson();
                contactperson.setContactPerson( rs.getString( "Contact_Person" ) );
                contactperson.setPhoneNumber( rs.getString( "Phone_Number" ) );
                contactperson.setCellNumber( rs.getString( "Cell_Number" ) );
                contactperson.setFaxNumber( rs.getString( "Fax" ) );
                contactperson.setEmail( rs.getString( "Email" ) );
                contactperson.setAddress( rs.getString( "Address" ) );
            }
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                if( success == true )
                {
                    conn.commit();
                }
                else
                {
                    conn.rollback();
                }
            }
            catch( SQLException ignore )
            {
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return contactperson;

    }

    public static HashMap getContactPersonDetailsMap( int customerId )
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        ContactPerson contactperson = null;
        HashMap contactpersonMap = new HashMap();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.getCustomerContactPersonDetailsMap" ) );
            pstmt.setInt( 1, customerId );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                contactperson = new ContactPerson();
                contactperson.setContactPerson( rs.getString( "Contact_Person" ) );
                contactperson.setPhoneNumber( rs.getString( "Phone_Number" ) );
                contactperson.setCellNumber( rs.getString( "Cell_Number" ) );
                contactperson.setFaxNumber( rs.getString( "Fax" ) );
                contactperson.setEmail( rs.getString( "Email" ) );
                contactperson.setAddress( rs.getString( "Address" ) );
                contactpersonMap.put( contactperson.getContactPerson(), contactperson );
            }
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                if( success == true )
                {
                    conn.commit();
                }
                else
                {
                    conn.rollback();
                }
            }
            catch( SQLException ignore )
            {
            }

            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        
        return contactpersonMap;
    }

    public static HashMap getAddressContactPersonDetailsMap( int addressId )
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        ContactPerson contactperson = null;
        HashMap contactpersonMap = new HashMap();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.getAddressContactPersonDetailsMap" ) );
            pstmt.setInt( 1, addressId );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                contactperson = new ContactPerson();
                contactperson.setContactPerson( rs.getString( "Contact_Person" ) );
                contactperson.setPhoneNumber( rs.getString( "Phone_Number" ) );
                contactperson.setCellNumber( rs.getString( "Cell_Number" ) );
                contactperson.setFaxNumber( rs.getString( "Fax" ) );
                contactperson.setEmail( rs.getString( "Email" ) );
                contactperson.setAddress( rs.getString( "Address" ) );
                contactpersonMap.put( contactperson.getContactPerson() , contactperson );
            }
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return contactpersonMap;
    }

    public static HashMap getAddressDetailsMap( int addressId, Address address )
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        HashMap addressMap = new HashMap();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.customer.getAddressContactPersonDetailsMap" ) );
            pstmt.setInt( 1, addressId );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                address.setContactPerson( rs.getString( "Contact_Person" ) );
                address.setPhoneNumber( rs.getString( "Phone_Number" ) );
                address.setCellNumber( rs.getString( "Cell_Number" ) );
                address.setFaxNumber( rs.getString( "Fax" ) );
                address.setEmail( rs.getString( "Email" ) );
                addressMap.put( rs.getString( "Contact_Person" ), address.getAddressAsString() );
            }
            success = true;

        }
        catch( Exception e )
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                if( success == true )
                {
                    conn.commit();
                }
                else
                {
                    conn.rollback();
                }
            }
            catch( SQLException ignore )
            {
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return addressMap;
    }

    public static void saveShipAlertEmails( DBTransaction dbTransaction, Customer customer ) throws ScalarException
    {
        Address shippingAddress = customer.getShippingAddress();
        ArrayList moreShippingAddressList = customer.getShippingLocations();
        StringBuffer emails = new StringBuffer();

        if( ( shippingAddress != null ) && ( shippingAddress.getReceiveShipAlert() != null ) )
        {
            emails.append( shippingAddress.getEmail() ).append( "," );
        }
        if( ( moreShippingAddressList != null ) && ( moreShippingAddressList.isEmpty() == false ) )
        {
            Address address = null;
            int size = moreShippingAddressList.size();
            for( int i = 0; i < size; i++ )
            {
                address = ( Address ) moreShippingAddressList.get( i );
                if( ( address != null ) && ( address.getReceiveShipAlert() != null ) )

                {
                    emails.append( address.getEmail() ).append( "," );
                }
            }
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "DELETE FROM CUSTOMER_SHIP_ALERT_EMAILS_TABLE WHERE CUSTOMER_NAME = ?" );
            pstmt.setString( 1, customer.getCustomerName().trim() );
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement( "INSERT CUSTOMER_SHIP_ALERT_EMAILS_TABLE VALUES(?, ?)" );
            pstmt.setString( 1, customer.getCustomerName().trim() );
            pstmt.setString( 2, emails.toString() );
            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not update the customer ship alert emails information", sqle );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
    }

    /**
     * This method checks and returns if the given address ID is that of an end user address
     *
     * @param dbTransaction
     * @param addressID
     * @return boolean
     * @throws ScalarException
     */
    public static boolean isEndUserAddressID( DBTransaction dbTransaction, int addressID ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        boolean isEndUserAddress = false;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall( "{ CALL SP_CT_SLS_CHECK_END_USER_ADDRESS_ID(?,?) }" );
            cstmt.setInt(1, addressID );
            cstmt.registerOutParameter( 2, Types.INTEGER);
            rset = cstmt.executeQuery();
            if( cstmt.getInt( 2 ) == 0 )
            {
               isEndUserAddress = false;
            }
            else
            {
               isEndUserAddress = true;
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not check if the given address Id is that of an end user", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (cstmt != null)
            {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return isEndUserAddress;
    }

    /**
     * This method will return the customer number of the given customer ID
     *
     * @param dbTransaction
     * @param customerID
     * @return string
     * @throws ScalarException
     */
    public static String getCustomerNumber( DBTransaction dbTransaction, int customerID ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        String customerNumber = "";
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall( "SELECT C_Number FROM CUSTOMER WHERE Customer_ID = ?" );
            cstmt.setInt(1, customerID );
            rset = cstmt.executeQuery();
            if( rset.next() )
            {
                customerNumber = rset.getString( "C_Number" );
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("could not retrive customer number", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (cstmt != null)
            {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return customerNumber;
    }

    /**
     * This method checks and returns if the given customer number is already existing.
     *
     * @param dbTransaction
     * @param customerNumber
     * @return boolean
     * @throws ScalarException
     */
    public static boolean isCustomerNumberExists( DBTransaction dbTransaction, String customerNumber ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        boolean customerNumberExists = false;
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            cstmt = conn.prepareCall( "SELECT 1 FROM CUSTOMER WHERE C_NUMBER = ? AND C_DISABLED!=0" );
            cstmt.setString(1, customerNumber );
            rset = cstmt.executeQuery();

            if( rset.next() )
            {
                customerNumberExists = true;
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not check if the given customer number is already existing", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (cstmt != null)
            {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return customerNumberExists;
    }

    /**
     * This method will return the customer Id of the given customer number
     *
     * @param dbTransaction
     * @param customerNumber
     * @return customerID
     * @throws ScalarException
     */
    public static int getCustomerID( DBTransaction dbTransaction, String customerNumber ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        int customerID = -1;


        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT Customer_ID FROM CUSTOMER WHERE C_Number = ?" );
            pstmt.setString( 1, customerNumber );
            rset = pstmt.executeQuery();
            if( rset.next() )
            {
                customerID = rset.getInt( "Customer_ID" );
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("could not retrive customer ID", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if ( pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return customerID;
    }

    public static int getCustomerIDByCName( DBTransaction dbTransaction, String customerName ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        int customerID = -1;


        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT Customer_ID FROM CUSTOMER WHERE C_Name = ?" );
            pstmt.setString( 1, customerName );
            rset = pstmt.executeQuery();
            if( rset.next() )
            {
                customerID = rset.getInt( "Customer_ID" );
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("could not retrive customer ID", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if ( pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return customerID;
    }

    /**
     * Returns the address ID of the customer address with the given epicore shipping address code.
     * It will return -1 if the address is not existing
     *
     * @param dbTransaction
     * @param customerID
     * @param epicoreShipToCode
     * @return addressID
     * @throws ScalarException
     */

    public static int getAddressID( DBTransaction dbTransaction, int customerID, String epicoreShipToCode ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        int addressID = -1;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( "select Address_ID from Address where ( Address_ID in ( select Address_Id from Customer_Shipping_Address_Table where customer_id = ? ) or Address_ID = ( select C_Default_Shipping_Address_ID from customer where customer_id = ? ) or Address_ID = ( select C_Default_Billing_Address_ID from customer where customer_id = ? ) ) and Epicore_Ship_Code = ? " );
            pstmt.setInt(    1, customerID );
            pstmt.setInt(    2, customerID );
            pstmt.setInt(    3, customerID );
            pstmt.setString( 4, epicoreShipToCode );
            rset = pstmt.executeQuery();
            while( rset.next() )
            {
                addressID = rset.getInt( "Address_ID" );
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException( "Error while retreiving the address ID based on the epicore shipping code", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if ( pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return addressID;
    }

    public static ArrayList loadAllSalesReps() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList salesReps = new ArrayList ();
        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ( "select DISTINCT Sales_Rep_Name from Sales_Rep_Table where ( Deactivated = 0 or Deactivated is NULL )"  );

            rs = pstmt.executeQuery ();
            while ( rs.next () )
            {
                String sales_rep_name = rs.getString ( "Sales_Rep_Name" );
                salesReps.add ( new LabelValueBean ( sales_rep_name, sales_rep_name ) );
            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get Sales Rep from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return salesReps;
    }

    public static ArrayList loadAllSalesRepsforCustomer(int customerId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList salesReps = new ArrayList ();

        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ( SqlQueryMappings.get( "sales.customer.getSalesRepsForCustomer" ) );
            pstmt.setInt(1,customerId);
            rs = pstmt.executeQuery ();
            while ( rs.next () )
            {
                String sales_rep_name = rs.getString ( "Sales_Rep_Name" );
                salesReps.add ( new org.apache.struts.util.LabelValueBean ( sales_rep_name, sales_rep_name ) );
            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get Sales Reps for Customer from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return salesReps;
    }

    public static ArrayList loadAllSalesPersonsforCustomer( int customerId ) throws ScalarException
    {
        ArrayList list = new ArrayList();
        Iterator salesPersonsList = NewCustomerDAO.getAllSalesPersonsList( customerId ).iterator();
        SalesPerson salesPerson = null;
        try
        {
            while( salesPersonsList.hasNext() )
            {
                salesPerson = ( SalesPerson ) salesPersonsList.next();
                if( "checked".equalsIgnoreCase( salesPerson.getFlag() ) )
                {
                    list.add( new LabelValueBean( salesPerson.getSalesPerson(), salesPerson.getSalesPerson() ) );
                }
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }
        return list;
    }

    public static ArrayList getAllEndUserNames() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList endUsers = new ArrayList();
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "SELECT DISTINCT C.C_NAME FROM SALES_ORDER SO, CUSTOMER C WHERE SO_ODM_ID = C.CUSTOMER_ID" );
            rs = pstmt.executeQuery();
            String label = MessageResources.getMessageResources( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage( "label.sales.endCustomer" );
            endUsers.add( new org.apache.struts.util.LabelValueBean( "All " + label + "s", "0" ) );
            while( rs.next() )
            {
                endUsers.add( new LabelValueBean( rs.getString( "C_NAME" ), rs.getString( "C_NAME" ) ) );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get EndUsers from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return endUsers;

    }

    public static void getCustomerDetailsForAcknowledgement( DBTransaction dbTransaction, Customer customer ) throws ScalarException
    {
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            if( dbTransaction == null )
            {
                con = DataSource.getConnection();
            }
            else
            {
                con = dbTransaction.getConnection();
            }
            pstmt = con.prepareStatement( SqlQueryMappings.get( "sales.customer.getCustomerForOrderAcknowledgement" ) );
            pstmt.setInt( 1, customer.getCustomerId() );
            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                customer.setContractNumber( rs.getString( "C_Contract_Number" ) );
                customer.setCustomRequirements( rs.getInt( "C_Custom_Requirements" ) == 0 ? true:false );
                customer.setSupplierNumber( rs.getString( "C_Supplier_Number" ) );
            }
            success = true;
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Customer from database", sqlex );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( con != null )
                    {
                        if( success == true )
                        {
                            con.commit();
                        }
                        else
                        {
                            con.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (con != null) {
                try { con.close(); } catch (SQLException excp) {}
            }
        }
    }

    public static ArrayList getAllCustomerTypeList( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        ArrayList customerTypeList = new ArrayList();

        try
        {
            if( dbTransaction == null )
            {
                con = DataSource.getConnection();
            }
            else
            {
                con = dbTransaction.getConnection();
            }
            pstmt = con.prepareStatement( SqlQueryMappings.get( "sales.customer.getAllCustomerTypeList" ) );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                customerTypeList.add( new LabelValueBean( rs.getString( "C_Type_Name" ), rs.getString( "C_Type_ID" ) ) );
            }
            success = true;

        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Customer Types from database", sqlex );
        }
        finally
        {

            if( dbTransaction == null )
            {
                try
                {
                    if( con != null )
                    {
                        if( success == true )
                        {
                            con.commit();
                        }
                        else
                        {
                            con.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (con != null) {
                try { con.close(); } catch (SQLException excp) {}
            }
        }
        return customerTypeList;
    }

    public static ArrayList getCustomerTypeList( DBTransaction dbTransaction, int customerId ) throws ScalarException {

       Connection con = null;
       ResultSet rs   = null;
       PreparedStatement pstmt = null;
       boolean success = false;
       ArrayList customerTypeList = new ArrayList();

       try
       {
           if( dbTransaction == null ) {
               con = DataSource.getConnection();
           }
           else {
               con = dbTransaction.getConnection();
           }
           pstmt = con.prepareStatement(SqlQueryMappings.get("sales.customer.getCustomerTypeList"));
           pstmt.setInt( 1, customerId );
           rs= pstmt.executeQuery();

           while ( rs.next() ) {
                customerTypeList.add( new LabelValueBean( rs.getString("C_Type_Name"),"" ) );
           }
           if ( customerTypeList.size() == 0  ) {
                    customerTypeList.add(  new LabelValueBean( "Customer","" ) );
           }
           success = true;
       }
       catch (Exception sqlex)
        {
             sqlex.printStackTrace();
             throw new ScalarException ("Could not get Customer Types from database", sqlex);
        }
        finally
        {

            if (dbTransaction == null)
            {
                try
                {
                    if ( con != null)
                    {
                        if ( success == true)
                            con.commit();
                        else
                            con.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (con != null)
            {
                try { con.close(); } catch (SQLException excp) {}
            }
       }
        return customerTypeList;
    }

    public static String[] loadCustomerTypeRelationShips( DBTransaction dbTransaction, int customerID ) throws ScalarException
    {

        Connection con = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        ArrayList selectedCustomerTypeList = new ArrayList();
        String[] selectedCustomerTypeArray = null;

        try
        {
            if( dbTransaction == null )
            {
                con = DataSource.getConnection();
            }
            else
            {
                con = dbTransaction.getConnection();
            }
            pstmt = con.prepareStatement( SqlQueryMappings.get( "sales.customer.loadCustomerTypeRelationShips" ) );
            pstmt.setInt( 1, customerID );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                selectedCustomerTypeList.add( rs.getInt( "C_Type_ID" ) + "" );
            }

            if( selectedCustomerTypeList.size() == 0 )
            {
                rs.close();
                rs = null;
                pstmt.close();
                pstmt = null;

                pstmt = con.prepareStatement( "SELECT C_Type_ID FROM Customer_Types WHERE C_Type_Name='Customer'" );
                rs = pstmt.executeQuery();
                while( rs.next() )
                {
                    selectedCustomerTypeList.add( rs.getInt( "C_Type_ID" ) + "" );
                }
            }
            selectedCustomerTypeArray = new String[selectedCustomerTypeList.size()];
            selectedCustomerTypeList.toArray( selectedCustomerTypeArray );
            success = true;
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Customer Type Relationships from database", sqlex );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( con != null )
                    {
                        if( success == true )
                        {
                            con.commit();
                        }
                        else
                        {
                            con.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (con != null) {
                try { con.close(); } catch (SQLException excp) {}
            }
        }

        return selectedCustomerTypeArray;
    }

    public static int insertCustomerTypes( DBTransaction dbTransaction, String[] selectedCustomerTypeList, int customerId ) throws ScalarException
    {
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        int total = 0;
        int error_code = 0;
        boolean success = false;
        boolean odm = false;
        boolean endCustomerId = false;

        try
        {
            if( dbTransaction == null )
            {
                con = DataSource.getConnection();
            }
            else
            {
                con = dbTransaction.getConnection();
            }
            for( int i = 0; i < selectedCustomerTypeList.length; i++ )
            {
                String s = selectedCustomerTypeList[i];
                if( s.equalsIgnoreCase( "2" ) )
                {
                    odm = true;
                }
                else if( s.equalsIgnoreCase( "3" ) )
                {
                    endCustomerId = true;
                }
            }
            pstmt = con.prepareStatement( SqlQueryMappings.get( "sales.customer.doesCustomerOdmRelationExists" ) );
            pstmt.setInt( 1, customerId );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                total = rs.getInt( "TOTAL" );
            }
            if( odm == false && total > 0 )
            {
                error_code = 1;
            }
            else
            {
                rs.close();
                rs = null;
                pstmt.close();
                pstmt = null;
                pstmt = con.prepareStatement( SqlQueryMappings.get( "sales.customer.doesCustomerEndCustomerRelationExists" ) );
                pstmt.setInt( 1, customerId );
                rs = pstmt.executeQuery();
                while( rs.next() )
                {
                    total = rs.getInt( "TOTAL" );
                }

                if( endCustomerId == false && total > 0 )
                {
                    error_code = 2;
                }
                else
                {
                    pstmt = con.prepareStatement( SqlQueryMappings.get( "sales.customer.deleteCustomerTypeRelationShips" ) );
                    pstmt.setInt( 1, customerId );
                    pstmt.executeUpdate();

                    pstmt.close();
                    pstmt = null;

                    pstmt = con.prepareStatement( SqlQueryMappings.get( "sales.customer.insertCustomerTypeRelationShips" ) );
                    for( int i = 0; i < selectedCustomerTypeList.length; i++ )
                    {
                        pstmt.setInt( 1, customerId );
                        pstmt.setInt( 2, Integer.parseInt( selectedCustomerTypeList[i] ) );
                        pstmt.executeUpdate();
                    }
                    success = true;
                }
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not insert Customer Types into database", sqlex );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( con != null )
                    {
                        if( success == true )
                        {
                            con.commit();
                        }
                        else
                        {
                            con.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (con != null) {
                try { con.close(); } catch (SQLException excp) {}
            }
        }
        return error_code;
    }

    public static String getCustomerName( DBTransaction dbTransaction, int customerID ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        String customerNumber = "";

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            cstmt = conn.prepareCall( "SELECT C_Name FROM CUSTOMER WHERE Customer_ID = ?" );
            cstmt.setInt(1, customerID );
            rset = cstmt.executeQuery();
            if( rset.next() )
            {
                customerNumber = rset.getString( "C_Name" );
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("could not retrive customer number", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (cstmt != null)
            {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return customerNumber;
    }

   public static Address getAddressWithContactPersonDetails( int addressId, String contactPersonName ) throws ScalarException
    {
        Address address = new Address();
        address = getAddress( addressId );

        ContactPerson contactPerson = null;
        if( contactPersonName != null && contactPersonName.trim().length() != 0 )
        {
             contactPerson = getAddressContactPersonDetails (addressId , contactPersonName);
        }
        if( contactPerson != null )
        {
            //address.setContactPerson( contactPersonName );
            address.setPhoneNumber( contactPerson.getPhoneNumber() );
            address.setCellNumber( contactPerson.getCellNumber() );
            address.setFaxNumber( contactPerson.getFaxNumber() );
            address.setEmail( contactPerson.getEmail() );
        }
        else
        {
            if ( address == null )
            {
                address = new Address();
            }
            address.setPhoneNumber( "" );
            address.setCellNumber( "" );
            address.setFaxNumber( "" );
            address.setEmail( "" );
        }
        address.setContactPerson( contactPersonName );
        return address;
    }

    /**
     * Get All the customers or customer by search parameters
     *
     * @param customerName Search paramter
     * @return customer list
     */
   public static ArrayList getAllCustomers(String customerName,String region,String repOffice, String customerType) throws ScalarException
   {

        String SEARCH_CUSTOMERS = "Select Customer_ID,C_name,C_Contact_Person,C_Region from Customer where C_Disabled=1";
        String SEARCH_STRING = null;

        customerName    = ( customerName == null ) ? "" : customerName.trim();
        region          = ( region == null ) ? "" : region.trim();
        repOffice       = ( repOffice == null ) ? "" : repOffice.trim();
        customerType    = ( customerType == null ) ? "" : customerType.trim();

       if((region.equals("")) && (repOffice.equals("")) && (customerName.equals("")) && ( "".equals( customerType) ) )
        {
           SEARCH_STRING = " ORDER BY C_Name";
        }
        else
        {
           SEARCH_STRING = " and C_Name like '"+  StringUtils.formatToHandleQuote(customerName) +"%'" ;
           if( region != null && region.trim().length() > 0 )
           {
              SEARCH_STRING = SEARCH_STRING + " and C_Region ='"+StringUtils.formatToHandleQuote(region)+"' " ;
           }
           if( repOffice != null && repOffice.trim().length() > 0 )
           {
              SEARCH_STRING = SEARCH_STRING + " and C_Rep_Office = '"+StringUtils.formatToHandleQuote(repOffice)+"' ";
           }
           SEARCH_STRING = SEARCH_STRING + " AND Customer.Customer_ID IN ( SELECT Customer_ID FROM Customer_Type_Relationship WHERE C_Type_ID LIKE " + ( "".equals(customerType) ? "'%'" : customerType ) + " )  Order by C_NAME ";
        }
           SEARCH_CUSTOMERS = SEARCH_CUSTOMERS + SEARCH_STRING;
       return ((ArrayList) getCustomers(SEARCH_CUSTOMERS));
   }

   public static Address getAddressWithContactPersonDetails( int addressId, String contactPersonName, int customerId ) throws ScalarException
    {
        Address address = new Address();

        address = getAddress( addressId );

        ContactPerson contactPerson = null;
        if( contactPersonName != null && contactPersonName.trim().length() != 0 )
        {
            address.setContactPerson( contactPersonName );
            contactPerson = getAddressContactPersonDetails (addressId , contactPersonName);
            if(contactPerson == null )
            {
               contactPerson  = new ContactPerson();
            }
        }
        else
        {
            //String customerName = getCustomerName( null, customerId );
            //address.setContactPerson(customerName);
            Address customerAddress = getAddress(SalesOrderDAO.getDefaultBillingAddressId(customerId));
            if ( customerAddress == null )
            {
                customerAddress = new Address();
            }
            address.setPhoneNumber(customerAddress.getPhoneNumber());
            address.setCellNumber(customerAddress.getCellNumber());
            address.setFaxNumber(customerAddress.getFaxNumber());
            address.setEmail(customerAddress.getEmail());
        }
        if( contactPerson != null )
        {
            //address.setContactPerson( contactPersonName );
            address.setPhoneNumber( contactPerson.getPhoneNumber() );
            address.setCellNumber( contactPerson.getCellNumber() );
            address.setFaxNumber( contactPerson.getFaxNumber() );
            address.setEmail( contactPerson.getEmail() );
        }
        address.setContactPerson( contactPersonName );
        return address;
    }
    public static ArrayList getAllCustomersOfTypeCustomer() throws ScalarException
    {
             //Getting the customer list from CT
            String GETALL_VENDORS = "select C.Customer_ID, C_Name, C_Contact_Person,C_Region from Customer C, Customer_Type_Relationship CTR where C_Disabled = 1 and C.Customer_ID = CTR.Customer_ID and CTR.C_Type_ID = 1 ORDER BY C_Name";
            return ( CustomerDAO.getCustomers(GETALL_VENDORS));

    }

    public static void insertShippingMethodInCT (DBTransaction dbTransaction, int customerId, String methodname, String account) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            if( methodname == null )
            {
                methodname = "";
            }
            if( account == null )
            {
                account = "";
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.insertShippingMethod"));

            pstmt.setInt    ( 1, customerId);
            pstmt.setString ( 2, methodname.trim());
            pstmt.setString ( 3, account.trim() );
            pstmt.executeUpdate();
            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not insert Method Name, \"" + methodname + "\" into database", sqle);
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if (conn != null)
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

    }

    public static void saveCustomerHolds( DBTransaction dbTransaction, Customer customer, String userName, boolean isSample ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( "{CALL SP_CREATE_UPDATE_CUSTOMER_HOLDS ( ?, ?, ?, ?, ?, ?, ? ) }" );
            pstmt.setInt    ( 1, customer.getCustomerId() );
            pstmt.setString ( 2, userName );
            if( isSample == false )
            {
                pstmt.setString ( 3, customer.getMarketingHold() );
                pstmt.setString ( 4, customer.getFinanceHold() );
                pstmt.setString ( 5, customer.getOperationsHold() );
                pstmt.setString ( 6, customer.getSalesHold() );
            }
            else
            {
                pstmt.setString ( 3, customer.getSampleMarketingHold() );
                pstmt.setString ( 4, customer.getSampleFinanceHold() );
                pstmt.setString ( 5, customer.getSampleOperationsHold() );
                pstmt.setString ( 6, customer.getSampleSalesHold() );    
            }
            pstmt.setInt ( 7, ( isSample ) ? 0 : 1 );
            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not update the customer holds information", sqle );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore ) {}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); } catch( SQLException ignore ){}
            }
            if( conn != null )
            {
                try { conn.close(); } catch( SQLException ignore ){}
            }
        }
    }

    public static void loadCustomerHolds( DBTransaction dbTransaction, Customer customer, boolean isSample ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        String holdByGroup      = null;
        String holdReasonCode   = null;

        String operationsHold   = "0";
        String salesHold        = "0";
        String financeHold      = "0";
        String marketingHold    = "0";
        
        int isSampleInt         = 1;

        try
        {
            isSampleInt = ( isSample ? 0 : 1 );

            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( "SELECT Hold_By_Group, Hold_Reason_Code FROM Customer_Hold_Table WHERE Customer_id = ? AND Is_Sample = ?" );

            pstmt.setInt( 1, customer.getCustomerId() );
            pstmt.setInt( 2, isSampleInt );

            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                holdByGroup     = rs.getString( "Hold_By_Group" );
                holdReasonCode  = rs.getString( "Hold_Reason_Code" );

                if( "OPERATIONS".equalsIgnoreCase( holdByGroup ) )
                {
                    operationsHold = holdReasonCode;
                }
                else if ( "SALES".equalsIgnoreCase( holdByGroup ) )
                {
                    salesHold = holdReasonCode;
                }
                else if ( "FINANCE".equalsIgnoreCase( holdByGroup ) )
                {
                    financeHold = holdReasonCode;
                }
                else if( "MARKETING".equalsIgnoreCase( holdByGroup ) )
                {
                    marketingHold = holdReasonCode;
                }
            }

            if( isSample == false )
            {
                customer.setOperationsHold( operationsHold );
                customer.setSalesHold( salesHold );
                customer.setFinanceHold( financeHold );
                customer.setMarketingHold ( marketingHold );
            }
            else
            {
                customer.setSampleOperationsHold( operationsHold );
                customer.setSampleSalesHold( salesHold );
                customer.setSampleFinanceHold( financeHold );
                customer.setSampleMarketingHold ( marketingHold );
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not load customer holds", sqlex);
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
   }

    public static Customer loadCustomerHolds( DBTransaction dbTransaction, int customerId, boolean isSample ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        String holdByGroup      = null;
        String holdReasonCode   = null;
        Customer customer       = new Customer();

        String operationsHold   = "0";
        String salesHold        = "0";
        String financeHold      = "0";
        String marketingHold    = "0";

        int isSampleInt         = 1;
        try
        {
            isSampleInt = ( isSample ? 0 : 1 );

            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            customer.setOperationsHold( "0" );
            customer.setSalesHold( "0" );
            customer.setFinanceHold( "0" );
            customer.setMarketingHold ( "0" );

            pstmt = conn.prepareStatement ( "SELECT Hold_By_Group, Hold_Reason_Code FROM Customer_Hold_Table WHERE Customer_ID = ? AND Is_Sample = ?" );
            pstmt.setInt ( 1, customerId );
            pstmt.setInt ( 2, isSampleInt );

            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                holdByGroup     = rs.getString( "Hold_By_Group" );
                holdReasonCode  = rs.getString( "Hold_Reason_Code" );

                if( "OPERATIONS".equalsIgnoreCase( holdByGroup ) )
                {
                    operationsHold = holdReasonCode;
                }
                else if ( "SALES".equalsIgnoreCase( holdByGroup ) )
                {
                    salesHold = holdReasonCode;
                }
                else if ( "FINANCE".equalsIgnoreCase( holdByGroup ) )
                {
                    financeHold = holdReasonCode;
                }
                else if( "MARKETING".equalsIgnoreCase( holdByGroup ) )
                {
                    marketingHold = holdReasonCode;
                }
            }

            customer.setOperationsHold( operationsHold );
            customer.setSalesHold( salesHold );
            customer.setFinanceHold( financeHold );
            customer.setMarketingHold ( marketingHold );

            /*if( isSampleInt == 0 )
            {
                customer.setSampleOperationsHold( operationsHold );
                customer.setSampleSalesHold( salesHold );
                customer.setSampleFinanceHold( financeHold );
                customer.setSampleMarketingHold ( marketingHold );
            }
            else
            {
                customer.setOperationsHold( operationsHold );
                customer.setSalesHold( salesHold );
                customer.setFinanceHold( financeHold );
                customer.setMarketingHold ( marketingHold );
            }*/
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not load customer holds", sqlex);
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return customer;
   }

    public static ArrayList getAllCustomerNamesByUserType( DBTransaction dbTransaction, User user ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList customers = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            if ( user != null && user.getUserType() == Constants.USER_TYPE_CUSTOMER )
            {
                pstmt = conn.prepareStatement ( "select Customer_ID,C_Name from Customer where C_Disabled = 1 and customer_id = ?" );
                pstmt.setInt ( 1, user.getCustomerId() );
            }
            else if ( user != null && user.getUserType() == Constants.USER_TYPE_SALES_PERSON )
            {
                pstmt = conn.prepareStatement ( "SELECT C.CUSTOMER_ID, C.C_NAME FROM CUSTOMER C, CUSTOMER_SALESPERSON_RELATIONSHIP CSR WHERE C_DISABLED = 1 AND CSR.CUSTOMER_ID = C.CUSTOMER_ID AND CSR.SALESPERSON_ID IN (SELECT SALESPERSON_ID FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES( ? )) AND RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active') ORDER BY C_Name" );
                pstmt.setInt ( 1, user.getSalesPersonId() );
                customers.add(new org.apache.struts.util.LabelValueBean("All Customers","0"));
            }
            else
            {
                pstmt = conn.prepareStatement("select Customer_ID,C_Name from Customer where C_Disabled = 1 ORDER BY C_Name");
                customers.add(new org.apache.struts.util.LabelValueBean("All Customers","0"));
            }
            rs = pstmt.executeQuery();
            while(rs.next())
            {
                int customerId = rs.getInt("Customer_ID");
                customers.add(new org.apache.struts.util.LabelValueBean(rs.getString("C_Name"),Integer.toString(customerId)));
            }

        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Customers from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return customers;
   }

    public static ArrayList getAllDeliveryTerms ( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        ArrayList deliveryTerms = new ArrayList();

        try
        {
            if( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get("sales.salesOrder.getAllDeliveryTerms") );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                deliveryTerms.add ( new LabelValueBean ( rset.getString ( "DELIVERY_TERMS" ), rset.getString ( "DELIVERY_TERMS_ID" ) ));
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not load Delivery Terms", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){};
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){};
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){};
            }
        }
        return deliveryTerms;
    }
    public static Address getAddressWithContactPersonDetails( int addressId ) throws ScalarException
    {
        Address address = null;
        address = getAddress( addressId );
        if ( address != null )
        {
            String contactPersonName = address.getContactPerson();

            ContactPerson contactPerson = null;
            if( contactPersonName != null && contactPersonName.trim().length() != 0 )
            {
                 contactPerson = getAddressContactPersonDetails (addressId , contactPersonName);
            }
            if( contactPerson != null )
            {
                address.setPhoneNumber( contactPerson.getPhoneNumber() );
                address.setCellNumber( contactPerson.getCellNumber() );
                address.setFaxNumber( contactPerson.getFaxNumber() );
                address.setEmail( contactPerson.getEmail() );
            }
            else
            {
                address.setContactPerson( "" );
                address.setPhoneNumber( "" );
                address.setCellNumber( "" );
                address.setFaxNumber( "" );
                address.setEmail( "" );
            }
        }
        return address;
    }
    public static Address getBillingAddress ( int addressId ) throws ScalarException
    {
        Address address = null;
        address = getAddress( addressId );
        if ( address != null )
        {
            if ( address.getContactPerson() == null || address.getContactPerson().trim().length() == 0 ) //NO CONTACT PERSON..WE ARE SETTING phone, fax, email of customer
            {
                address.setCellNumber( "" ); //since we don't have the cell number field for customer
                address.setContactPerson( "" );
            }
            else //set contact person details..
            {
                ContactPerson contactPerson = getAddressContactPersonDetails ( addressId , address.getContactPerson() );
                if( contactPerson != null )
                {
                    address.setPhoneNumber( contactPerson.getPhoneNumber() );
                    address.setCellNumber( contactPerson.getCellNumber() );
                    address.setFaxNumber( contactPerson.getFaxNumber() );
                    address.setEmail( contactPerson.getEmail() );
                }
                else
                {
                    address.setContactPerson( "" );
                    address.setPhoneNumber( "" );
                    address.setCellNumber( "" );
                    address.setFaxNumber( "" );
                    address.setEmail( "" );
                }
            }
        }
        return address;
    }

    public static ArrayList loadAllEndUserNames( DBTransaction dbTransaction, int salesOrderOrQuotation ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;

        ArrayList endUsers = new ArrayList();

        try
        {
            if( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            if( salesOrderOrQuotation == 0  )
            {
                pstmt = conn.prepareStatement( "SELECT DISTINCT C.CUSTOMER_ID, C.C_NAME FROM SALES_ORDER SO, CUSTOMER C WHERE SO_ODM_ID = C.CUSTOMER_ID" );
            }
            else
            {
                pstmt = conn.prepareStatement( "SELECT DISTINCT C.CUSTOMER_ID, C.C_NAME FROM QUOTATION_TABLE QT, CUSTOMER C WHERE Q_ODM_ID = C.CUSTOMER_ID" );
            }

            rs = pstmt.executeQuery();
            String label = MessageResources.getMessageResources( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage( "label.sales.endCustomer" );
            endUsers.add( new LabelValueBean( "All " + label + "s", "0" ) );
            while( rs.next() )
            {                                           
                endUsers.add( new LabelValueBean( rs.getString( "C_NAME" ), rs.getString( "CUSTOMER_ID" ) ) );
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get EndUsers from database", sqlex);
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return endUsers;
    }

    public static ArrayList loadCustomerAllContactPersonsDetails( DBTransaction dbTransaction, int customerId ) throws ScalarException
    {
        Connection conn             = null;
        PreparedStatement pstmt     = null;
        ResultSet rs                = null;
        ContactPerson contactperson = null;
        ArrayList contactpersonList = new ArrayList();

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            //This statement is for getting the Customer Contact person emails
            pstmt = conn.prepareStatement( "SELECT DISTINCT Contact_Person, Email, Contact_Person_Type, Display_Name, Sales_Ack_Mail, Proforma_Invoice_Mail, Packing_Slip_Mail, Comm_Invoice_Mail, Quote_Ack_Mail, RMA_ACK_MAIL  FROM fn_load_customer_all_contact_persons_details( ? ) WHERE  (Email IS NOT NULL) AND (Email <> '')");
            pstmt.setInt ( 1, customerId );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                contactperson = new ContactPerson();
                
                contactperson.setContactPerson  ( rs.getString( "Contact_Person" ) );
                contactperson.setEmail          ( rs.getString( "Email" ) );
                contactperson.setContactPersonType( rs.getString( "Display_Name" ) );
                contactperson.setSalesAckMail   ( rs.getString( "Sales_Ack_Mail" ) != null &&  rs.getString("Sales_Ack_Mail").equals("0") ? true : false );
                contactperson.setProformaInvoice( rs.getString( "Proforma_Invoice_Mail" ) != null &&  rs.getString("Proforma_Invoice_Mail").equals("0") ? true : false );
                contactperson.setPackingSlip    ( rs.getString( "Packing_Slip_Mail" ) != null &&  rs.getString("Packing_Slip_Mail").equals("0") ? true : false );
                contactperson.setCommerInvoice  ( rs.getString( "Comm_Invoice_Mail" ) != null &&  rs.getString("Comm_Invoice_Mail").equals("0") ? true : false );
                contactperson.setQuoteAckMail   ( rs.getString( "Quote_Ack_Mail" ) != null &&  rs.getString("Quote_Ack_Mail").equals("0") ? true : false );
                contactperson.setRmaAckMail     ( rs.getString( "RMA_ACK_MAIL" ) != null &&  rs.getString("RMA_ACK_MAIL").equals("0") ? true : false );

                contactpersonList.add( contactperson );
            }
        }
        catch (Exception sqlex)
        {
             sqlex.printStackTrace();
             throw new ScalarException ("Could not get ContactPersons from database", sqlex);
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return contactpersonList;
    }

    public static String getCustomerContactPersonEmailsForRMA( DBTransaction dbTransaction, int customerId ) throws ScalarException
    {
        String cpEmails             = null;
        ArrayList cpList            = null;
        ContactPerson contactPerson = null;
        try
		{
            cpList = loadCustomerAllContactPersonsDetails( dbTransaction, customerId );
            if( cpList != null && cpList.size() > 0 )
            {
                for ( int i = 0; i < cpList.size(); i++ )
                {
                    contactPerson = ( ContactPerson ) cpList.get ( i );
                    if( contactPerson.isRmaAckMail() )
                    {
                        if ( cpEmails == null || cpEmails.trim().length() == 0 )
                        {
                            cpEmails = contactPerson.getEmail() ;
                        }
                        else
                        {
                           cpEmails = cpEmails + Mailer.EMAIL_SEPARATOR + contactPerson.getEmail();
                        }
                    }
                }
            }

        }
        catch (Exception sqlex)
        {
             sqlex.printStackTrace();
             throw new ScalarException ("Could not get RMA ContactPersons emails from database", sqlex);
        }
        return cpEmails;
    }

    public static ArrayList getAllCustomerNamesByUserTypeByCustomerType( DBTransaction dbTransaction, User user , int isSample) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList customers = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            if (isSample==1)
            {
                if ( user != null && user.getUserType() == Constants.USER_TYPE_CUSTOMER )

                {
                    pstmt = conn.prepareStatement ( "select Customer_ID,C_Name from Customer where C_Disabled = 1 and customer_id = ? and is_sample = 1" );
                    pstmt.setInt ( 1, user.getCustomerId() );
                }
                else if ( user != null && user.getUserType() == Constants.USER_TYPE_SALES_PERSON )
                {
                    pstmt = conn.prepareStatement ( "SELECT C.CUSTOMER_ID, C.C_NAME FROM CUSTOMER C, CUSTOMER_SALESPERSON_RELATIONSHIP CSR WHERE C_DISABLED = 1 AND CSR.CUSTOMER_ID = C.CUSTOMER_ID AND  C.IS_SAMPLE= 1 AND CSR.SALESPERSON_ID IN (SELECT SALESPERSON_ID FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES( ? )) AND RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active') ORDER BY C_Name" );
                    pstmt.setInt ( 1, user.getSalesPersonId() );
                    customers.add(new org.apache.struts.util.LabelValueBean("All Customers","0"));
                }
                else
                {
                    pstmt = conn.prepareStatement("select Customer_ID,C_Name from Customer where C_Disabled = 1 and IS_SAMPLE = 1 ORDER BY C_Name");
                    customers.add(new org.apache.struts.util.LabelValueBean("All Customers","0"));
                }
            }
            else 
            {
                 if ( user != null && user.getUserType() == Constants.USER_TYPE_CUSTOMER )
                {
                    pstmt = conn.prepareStatement ( "select Customer_ID,C_Name from Customer where C_Disabled = 1 and customer_id = ?" );
                    pstmt.setInt ( 1, user.getCustomerId() );
                }
                else if ( user != null && user.getUserType() == Constants.USER_TYPE_SALES_PERSON )
                {
                    pstmt = conn.prepareStatement ( "SELECT C.CUSTOMER_ID, C.C_NAME FROM CUSTOMER C, CUSTOMER_SALESPERSON_RELATIONSHIP CSR WHERE C_DISABLED = 1 AND CSR.CUSTOMER_ID = C.CUSTOMER_ID AND CSR.SALESPERSON_ID IN (SELECT SALESPERSON_ID FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES( ? )) AND RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active') ORDER BY C_Name" );
                    pstmt.setInt ( 1, user.getSalesPersonId() );
                    customers.add(new org.apache.struts.util.LabelValueBean("All Customers","0"));
                }
                else
                {
                    pstmt = conn.prepareStatement("select Customer_ID,C_Name from Customer where C_Disabled = 1 ORDER BY C_Name");
                    customers.add(new org.apache.struts.util.LabelValueBean("All Customers","0"));
                }
            }
                rs = pstmt.executeQuery();
                while(rs.next())
                {
                    int customerId = rs.getInt("Customer_ID");
                    customers.add(new org.apache.struts.util.LabelValueBean(rs.getString("C_Name"),Integer.toString(customerId)));
                }

        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Customers from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return customers;
   }

   public static ArrayList getAllCustomers(String customerName, User user, int isSample) throws ScalarException
    {
        if( customerName != null ){
            customerName = customerName.trim() ;
        }

        //Getting the customer list from CT
        String SEARCH_CUSTOMERS = "";
        if ( user != null && user.getUserType() == Constants.USER_TYPE_CUSTOMER )
        {
            if(isSample ==1)
            {
               SEARCH_CUSTOMERS = "Select Customer_ID,C_Name,C_Contact_Person,C_Region from Customer Where C_Name like '" + StringUtils.formatToHandleQuote(customerName) + "%' and C_Disabled = 1 and is_sample = 1 and CUSTOMER_ID = " + user.getCustomerId() + " ORDER BY C_Name";
            }
            else
            {
               SEARCH_CUSTOMERS = "Select Customer_ID,C_Name,C_Contact_Person,C_Region from Customer Where C_Name like '" + StringUtils.formatToHandleQuote(customerName) + "%' and C_Disabled = 1 and CUSTOMER_ID = " + user.getCustomerId() + " ORDER BY C_Name";
            }
        }

        else if ( user != null & user.getUserType() == Constants.USER_TYPE_SALES_PERSON )
        {
            SEARCH_CUSTOMERS = "Select C.Customer_ID,C.C_Name,C.C_Contact_Person,C.C_Region from Customer C, CUSTOMER_SALESPERSON_RELATIONSHIP CSR, CUSTOMER_TYPE_RELATIONSHIP CTR Where C.C_Name like '" + StringUtils.formatToHandleQuote(customerName) + "%' and C.C_Disabled = 1 AND CSR.CUSTOMER_ID = C.CUSTOMER_ID AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 1 AND CSR.SALESPERSON_ID IN (SELECT SALESPERSON_ID FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES(" + user.getSalesPersonId() + ")) AND CSR.RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active') ";
            if ( ConfigManager.isLoadEndCustomersForNewSales() )
            {
                SEARCH_CUSTOMERS += " UNION Select C.Customer_ID,C.C_Name,C.C_Contact_Person,C.C_Region from Customer C, CUSTOMER_SALESPERSON_RELATIONSHIP CSR, CUSTOMER_TYPE_RELATIONSHIP CTR Where C.C_Name like '" + StringUtils.formatToHandleQuote(customerName) + "%' and C.C_Disabled = 1 AND CSR.CUSTOMER_ID = C.CUSTOMER_ID AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 2 AND CSR.SALESPERSON_ID IN (SELECT SALESPERSON_ID FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES(" + user.getSalesPersonId() + ")) AND CSR.RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active') ";
            }
            if ( ConfigManager.isLoadEndUsersForNewSales() )
            {
                SEARCH_CUSTOMERS += " UNION Select C.Customer_ID,C.C_Name,C.C_Contact_Person,C.C_Region from Customer C, CUSTOMER_SALESPERSON_RELATIONSHIP CSR, CUSTOMER_TYPE_RELATIONSHIP CTR Where C.C_Name like '" + StringUtils.formatToHandleQuote(customerName) + "%' and C.C_Disabled = 1 AND CSR.CUSTOMER_ID = C.CUSTOMER_ID AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 3 AND CSR.SALESPERSON_ID IN (SELECT SALESPERSON_ID FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES(" + user.getSalesPersonId() + ")) AND CSR.RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active') ";
            }
            if(isSample ==1)
            {
                SEARCH_CUSTOMERS += "and is_sample=1";
            }
            SEARCH_CUSTOMERS += " ORDER BY C_Name";
        }
        else
        {
            if(isSample==1)
            {
                SEARCH_CUSTOMERS = "Select C.Customer_ID, C.C_Name, C.C_Contact_Person, C.C_Region from Customer C, CUSTOMER_TYPE_RELATIONSHIP CTR Where C_Name like '" + StringUtils.formatToHandleQuote(customerName) + "%' and C_Disabled = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 1 and is_sample =1";
            }
            else
            {
                SEARCH_CUSTOMERS = "Select C.Customer_ID, C.C_Name, C.C_Contact_Person, C.C_Region from Customer C, CUSTOMER_TYPE_RELATIONSHIP CTR Where C_Name like '" + StringUtils.formatToHandleQuote(customerName) + "%' and C_Disabled = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 1 ";
            }
            if ( ConfigManager.isLoadEndCustomersForNewSales() )
            {
                SEARCH_CUSTOMERS += " UNION Select C.Customer_ID, C.C_Name, C.C_Contact_Person, C.C_Region from Customer C, CUSTOMER_TYPE_RELATIONSHIP CTR Where C_Name like '" + StringUtils.formatToHandleQuote(customerName) + "%' and C_Disabled = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 2 ";
            }
            if ( ConfigManager.isLoadEndUsersForNewSales() )
            {
                SEARCH_CUSTOMERS += " UNION Select C.Customer_ID, C.C_Name, C.C_Contact_Person, C.C_Region from Customer C, CUSTOMER_TYPE_RELATIONSHIP CTR Where C_Name like '" + StringUtils.formatToHandleQuote(customerName) + "%' and C_Disabled = 1 AND C.CUSTOMER_ID = CTR.CUSTOMER_ID AND C_TYPE_ID = 3 ";
            }
            SEARCH_CUSTOMERS += " ORDER BY C_Name";
        }
        return ( ( ArrayList ) getCustomers( SEARCH_CUSTOMERS ) );

    }

    public static ArrayList getAllCustomers(int isSample) throws ScalarException
    {
        String GETALL_VENDORS;
        if (isSample == 1 )
        {
            GETALL_VENDORS = "select Customer_ID,C_name,C_Contact_Person,C_Region from Customer WHERE C_Disabled = 1 and is_sample = 1 ORDER BY C_Name";
        }
        else
        {
            GETALL_VENDORS = "select Customer_ID,C_name,C_Contact_Person,C_Region from Customer WHERE C_Disabled = 1 ORDER BY C_Name";
        }
        return ((ArrayList) getCustomers(GETALL_VENDORS));
    }

    public static ArrayList getAllCustomerNames( int isSample ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList customers = new ArrayList();

        try
        {
            conn = DataSource.getConnection();

            if( isSample == 1 )
            {
                pstmt = conn.prepareStatement( "select Customer_ID,C_Name from Customer where C_Disabled = 1 and is_sample=1  ORDER BY C_Name" );
            }
            else
            {
                pstmt = conn.prepareStatement( "select Customer_ID,C_Name from Customer where C_Disabled = 1 ORDER BY C_Name" );
            }
            rs = pstmt.executeQuery();
            customers.add( new LabelValueBean( "All Customers", "0" ) );
            while( rs.next() )
            {
                int customerId = rs.getInt( "Customer_ID" );
                customers.add( new LabelValueBean( rs.getString( "C_Name" ), Integer.toString( customerId ) ) );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Customers from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return customers;

    }

    public static void setAddressContactPersonObject(ArrayList addresses) {
        if (addresses != null) {
            int shippingLocsSize = addresses.size();

            for (int a = 0; a < shippingLocsSize; a++) {
                Address address = (Address) addresses.get(a);
                ContactPerson contactPerson = getAddressContactPersonDetails(address.getAddressId(), address.getContactPerson());
                if (contactPerson == null) {
                    contactPerson = new ContactPerson();
                }
                address.setContactPersonObject(contactPerson);
            }
        }
    }

    /**
     *
     * @param dbTransaction
     * @param addresses
     * @param customerId
     * @param addressType "Shipping" or "Billing"
     * @throws ScalarException
     */
    public static void updateAddresses(DBTransaction dbTransaction, ArrayList addresses, int customerId, String addressType) throws ScalarException {
        if (addresses != null) {
            for (int locationNo = 0; locationNo < addresses.size(); locationNo++) {
                Address address = (Address) addresses.get(locationNo);
                int addressId = 0;
                if ("-1".equals(address.getContactPerson()) || "".equals(address.getContactPerson())) {
                    address.setContactPerson(null);
                }

                if ("true".equals(address.getRemove())) {
                    removeAddress(dbTransaction, customerId, address.getAddressId(), addressType);
                } else if (address.getAddressId() <= 0) {
                    //formats the address object for inserting into the CT database
                    address.formatForInsertingIntoCT();
                    //saving the address details only if it is not empty
                    if (!address.isAddressEmpty()) {
                        addressId = insertAddress(dbTransaction, address.getAddressId(), address.getAddress1(), address.getAddress2(), address.getAddress3(), address.getAddress4(), address.getCity(), address.getState(), address.getZipCode(), address.getCountry(), address.getContactPersonObject().getPhoneNumber(), address.getContactPersonObject().getFaxNumber(), address.getContactPersonObject().getEmail(), address.getContactPerson(), address.getContactPersonObject().getCellNumber(), true, "", address.getEpicoreShipCode(), "Billing".equalsIgnoreCase(addressType)?addressType:"SHIP1", address.getTagName());
                        updateAddressContactPerson(dbTransaction, addressId, address.getLocAddressId());
                        updateAddressContactPersonDetails(dbTransaction, addressId, address.getContactPerson(), address.getContactPersonObject().getPhoneNumber(), address.getContactPersonObject().getCellNumber(), address.getContactPersonObject().getFaxNumber(), address.getContactPersonObject().getEmail(), "");
                        updateCustomerAddressTable(dbTransaction, customerId, addressId, addressType);
                    }
                } else {
                    addressId = insertAddress(dbTransaction, address.getAddressId(), address.getAddress1(), address.getAddress2(), address.getAddress3(), address.getAddress4(), address.getCity(), address.getState(), address.getZipCode(), address.getCountry(), address.getContactPersonObject().getPhoneNumber(), address.getContactPersonObject().getFaxNumber(), address.getContactPersonObject().getEmail(), address.getContactPerson(), address.getContactPersonObject().getCellNumber(), false, "", address.getEpicoreShipCode(), "Billing".equalsIgnoreCase(addressType)?addressType:"SHIP1", address.getTagName());
                    updateAddressContactPersonDetails(dbTransaction, addressId, address.getContactPerson(), address.getContactPersonObject().getPhoneNumber(), address.getContactPersonObject().getCellNumber(), address.getContactPersonObject().getFaxNumber(), address.getContactPersonObject().getEmail(), "");
                }
                if ("true".equals(address.getDefaultAddress())) {
                    updateCustomerDefaultAddress(dbTransaction, customerId, addressId, addressType);
                }
            }
        }

    }
}